<?php

// +----------------------------------------------------------------------

// | Description: 客户

// +----------------------------------------------------------------------

// | Author: zl

// +----------------------------------------------------------------------



namespace app\crm\controller;



use app\admin\controller\ApiCommon;

use app\crm\model\BalanceLog;

use app\crm\model\Setting;

use app\crm\model\SettingCategory;

use app\crm\model\Service;

use app\crm\model\ReturnVist;

use app\crm\model\ReturnVistT;

use app\crm\model\Customer as CustomerModel;

use app\crm\model\TransferShop;

use app\crm\model\CustomerConfig as CustomerConfigModel;

use app\crm\model\CustomerGrade as CustomerGradeModel;

use app\crm\model\ReserveandLost;

use app\crm\model\OrderStatistics;

use think\response\Json;

use think\facade\Db;

use think\facade\Request;

use app\admin\model\Coupon as CouponModel;

use app\mini\model\MiniStisticsModel;

use app\common\traits\ActionRecordTrait;

use app\shop\model\unionpay\AppConfig;
use app\shop\model\unionpay\AppUtil;
use app\shop\model\unionpay\Test;


class Sendcard extends ApiCommon

{
    protected $customerModel;

    protected $customerConfigModel;

    /**

     * 用于判断权限

     * @permission 无限制

     * @allow 登录用户可访问

     * @other 其他根据系统设置

     **/

    public function initialize()

    {

        $action = [

            'permission' => ['send_vip_card','update_vip_info2','zidingyi','daochu_jindian','daochu_xinzeng','daochu_shishou','gengxin_kefu','tiaozheng_fuwukaxiang','huliliang_tongji','daochu_yubeihuiyuan','daorukaxiang2','daochu_weichengjiao','jion_vip_customer','zidingyi2','vip_info','zidingyi3','daochu_weijindian','update_shop_id','gengxin_yucun','daochu_refund_order','zidingyi4','zidingyi5','zidingyi6','get_paykey','daochu_2022_09','daochu_case','get_access_token','daochu_tag','loss_customers','baoyou_customer','daochu_liushibaoyou','cj_customer','zidingyi7','warning_customer','baoyou_customer1','lost_customer','hecha_hexiao','staff_performance','daochu_service_mingxi','daochu_service_log','shop_new_order','export_2023'],

            'allow' => ['users', 'info']

        ];

        event('CheckAuth',$action);

        $request = $this->request;

        $a = strtolower($request->action());



        $this->customerModel = new CustomerModel();

        $this->customerConfigModel = new CustomerConfigModel();

        if (!in_array($a, $action['permission'])) {

            parent::initialize();

        }

    }

    /**
     * 更新客服
     */
    public function gengxin_kefu()
    {
        // echo "批量更新";
        // exit;
        if (empty($_FILES['file'])) {

            echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/gengxinkefu.html">返回【会员基本信息导入】页面</a>';exit;

        }

        $tmp_name = $_FILES['file']['tmp_name'];

        // var_dump($tmp_name);

        // exit;

        $importData = readExcelData($tmp_name);

        echo "<pre>";
        print_r($importData);
        echo "</pre>";
        // exit;

        // $kefu_list = [
        //     '苏方平' => 441,
        //     '马克燕' => 519,
        //     '李梦娇' => 518,
        //     '付一鸣' => 523,
        //     '周梦琪' => 517,
        //     '杨玉秀' => 524,
        //     '刘芳芳' => 525,
        //     '秦荣钏' => 516,
        //     '凡大雪' => 521,
        //     '曹艳春' => 520,
        //     '魏李雪' => 527,
        //     '张淑玉' => 526,
        //     '张宗玉' => 528,
        //     '卢越' => 522,
        //     '万川源' => 947,
        //     '王雯雯' => 948
        // ];

        $kefu_list = Db::name('admin_user')->where('structure_id','8')->column('id','realname');

        $laiyuan_list = Db::name('setting')->where('category_id','4')->column('id','name');
        // echo "<pre>";
        // print_r($kefu_list);
        // echo "</pre>";

        // echo "<pre>";
        // print_r($laiyuan_list);
        // echo "</pre>";

        foreach ($importData as $k => $v) {
            $tel = $v['0'];

            $updateArr = [];

            if(!empty($v['1'])){

                $updateArr['source_id'] = $laiyuan_list[$v['1']];

            }

            if(!empty($v['2'])){

                $updateArr['creator_id'] = $kefu_list[$v['2']];
                $updateArr['operator'] = $kefu_list[$v['2']];

            }

            // echo "<pre>";
            // print_r($updateArr);
            // echo "</pre>";

            Db::name('customer')->where('telephone','=',$tel)->update($updateArr);
        }


    }

    /**
     * 更新预存
     */
    public function gengxin_yucun()
    {
        echo "更新预存";

        if (empty($_FILES['file'])) {

            echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/gengxin_yucun.html">返回【会员基本信息导入】页面</a>';exit;

        }

        $tmp_name = $_FILES['file']['tmp_name'];

        $importData = readExcelData($tmp_name);

        echo "<pre>";
        print_r($importData);
        echo "</pre>";

        $theDayBeforeYesterday = date('Ymd',strtotime('-2 day'));
        $yesterdayStr = date('Ymd',strtotime('-1 day'));
        $todayStr = date('Ymd',time());

        $shop_id = '73782775246614';
        // echo "前天：".$theDayBeforeYesterday;
        // echo "<br/>";
        // echo "昨天：".$yesterdayStr;
        // echo "<br/>";
        // echo "今天：".$todayStr;
        // echo "<br/>";
        // exit;

        $add_balance_tj = [];
        foreach ($importData as $key => $value) {
            $timeStr = [$todayStr];
            // echo "<pre>";
            // print_r($timeStr);
            // echo "</pre>";

            $where = [];
            array_push($where,['king_no','=',$value['0']]);
            $id = Db::name('customer')->where($where)->value('id');

            $bh_update_array = [
                'money' => $value['1'],
                'complimentary_money' => $value['2']
            ];

            $bh_where = [];
            array_push($bh_where,['customer_id','=',$id]);
            array_push($bh_where,['balance_category_id','=',1]);
            Db::name('customer_balance')->where($bh_where)->update($bh_update_array);



            $xm_update_array = [
                'money' => $value['3'],
                'complimentary_money' => $value['4']
            ];

            $xm_where = [];
            array_push($xm_where,['customer_id','=',$id]);
            array_push($xm_where,['balance_category_id','=',2]);
            Db::name('customer_balance')->where($xm_where)->update($xm_update_array);


            // -------------------------balance_tj--------------------------

            $bh_where_tj = [];

            array_push($bh_where_tj,['customer_id','=',$id]);
            array_push($bh_where_tj,['balance_category_id','=',1]);
            array_push($bh_where_tj,['time','in',$timeStr]);

            $res_bh = Db::name('customer_balance_tj')->where($bh_where_tj)->select()->toArray();

            if(!empty($res_bh)){
                Db::name('customer_balance_tj')->where($bh_where_tj)->update($bh_update_array);
            }else{
                $date = [
                    'customer_id'         => $id,
                    'balance_category_id' => 1,
                    'time'                => $todayStr,
                    'money'               => $value['1'],
                    'complimentary_money' => $value['2'],
                    'shop_id'             => $shop_id
                ];

                array_push($add_balance_tj,$date);
            }



            $xm_where_tj = [];

            array_push($xm_where_tj,['customer_id','=',$id]);
            array_push($xm_where_tj,['balance_category_id','=',2]);
            array_push($xm_where_tj,['time','in',$timeStr]);

            $res_xm = Db::name('customer_balance_tj')->where($xm_where_tj)->select()->toArray();

            if(!empty($res_xm)){
                Db::name('customer_balance_tj')->where($xm_where_tj)->update($xm_update_array);
            }else{
                $date = [
                    'customer_id'         => $id,
                    'balance_category_id' => 2,
                    'time'                => $todayStr,
                    'money'               => $value['3'],
                    'complimentary_money' => $value['4'],
                    'shop_id'             => $shop_id
                ];

                array_push($add_balance_tj,$date);
            }


        }

        // echo "<pre>";
        // print_r($add_balance_tj);
        // echo "</pre>";
        // exit;

        $res = Db::name("customer_balance_tj")->insertAll($add_balance_tj);
    }


    /**

     * 即时发送会员福利券

     */
    public function send_vip_card()
    {

        $time = time();
        $customer_id = '284103';
        // $customer_id = '123883';
        // $customer_id = $request['uid'];
		echo '发卡';

        $card_id = ['19','20','21','22','23','24','25','26','27','28'];
        // $card_id = ['4','5','6'];

        $customerList = Db::name('customer')->where('id','=',$customer_id)->select();


        $cardList = array();


        if(floor($customerList[0]['consumption'])>='5000' && floor($customerList[0]['consumption'])<'15000'){
            array_push($cardList,'19','22');
        }

        if(floor($customerList[0]['consumption'])>='15000' && floor($customerList[0]['consumption'])<'50000'){
            array_push($cardList,'20','23','25','26');
        }

        if(floor($customerList[0]['consumption'])>='50000'){
            array_push($cardList,'21','24','27','28');
        }


        $i=0;
        $r=0;

        $provideCustomerList = array();


        $repeatArr = array();


        foreach ($cardList as $k => $v) {
            $isSend = Db::name('coupon_provide_customer')->where('customer_id','=',$customer_id)->where('coupon_id','in',$card_id)->select()->toArray();

            if(empty($isSend)){

                $provideCustomerList[$i]['coupon_id'] = $v;


                $provideCustomerList[$i]['create_time'] = $time;


                $provideCustomerList[$i]['customer_id'] = $customer_id;


                $provideCustomerList[$i]['staus'] = '0';

                $i++;

            }else{

                $repeatArr[$r]['coupon_id'] = $v;


                $r++;

            }


        }

        echo "<pre>";
        print_r($provideCustomerList);
        echo "</pre>";

        echo "<pre>";
        print_r($repeatArr);
        echo "</pre>";

        if(!empty($provideCustomerList)){
            Db::name('coupon_provide_customer')->insertAll($provideCustomerList);

            echo "发放成功！！！";
        }else{
            echo "顾客本季度已经发放过卡券了！";
        }

    }




    /**
     * 调整顾客会员等级
     */
    public function update_vip_info2()
    {
        if (empty($_FILES['file'])) {

            echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/huiyuantiaozheng.html">返回【会员基本信息导入】页面</a>';exit;

        }

        // echo "<pre>";
        // print_r($_FILES['file']);
        // echo "</pre>";

        $tmp_name = $_FILES['file']['tmp_name'];

        $importData = readExcelData($tmp_name);

        // echo "<pre>";
        // print_r($importData);
        // echo "</pre>";
        // exit;

        foreach ($importData as $key => $value) {
            $customer_id = Db::name('customer')
                            ->where('king_no','=',$value['3'])
                            ->where('telephone','=',$value['2'])
                            ->value('id');

            // echo $value['0']."的ID为：".$customer_id;
            // echo "<br/>";
            $updateArr = array();

            $updateArr = [

                'join_time' => strtotime($value['5']),

                'be_overdue_time' => strtotime($value['6']),

                'membership_leve' => $value['4'],

                'consumption' => $value['7'] ?? '',

                'member_classification' => $value['4'],

            ];

            echo "<pre>";
            print_r($updateArr);
            echo "</pre>";

            Db::name('customer')->where('id','=',$customer_id)->update($updateArr);

            // exit;

        }
    }

    public function zidingyi()
    {
        $qichu_time = strtotime('2022-01-01 00:00:00');

        $time1 = strtotime('-1 month');

        // echo $time1;
        // exit;



        // 优案且已经毕业的顾客
        $yx_where = [];
        // $my_where = [];

        array_push($yx_where,['graduation_type','=','1']);
        array_push($yx_where,['selection_results','in',[2,3]]);

        $yx_customer_list = Db::name('case')->where($yx_where)->column('distinct customer_id');

        // echo "<pre>";
        // print_r($yx_customer_list);
        // exit;

        $client_list = [];

        foreach ($yx_customer_list as $k => $v) {
            $where = [];

            array_push($where,['customer_id','=',$v]);

            $caseList = Db::name('case')->where($where)->column('id,customer_id,selection_results,tg_excellent_time,graduation_type,position_id,position_name');

            $anli_num = count($caseList);

            // echo "<pre>";
            // print_r($caseList);

            if($anli_num<2){
                array_push($client_list,$v);
            }else{
                $position_list=[];// 部位
                $max_time = 0;// 最新评选时间
                $graduation_type = 0;// 毕业状态
                $selection_results = 0;// 评选结果
                foreach ($caseList as $k1 => $v1) {
                    if(in_array($v1['position_id'],$position_list)){
                        // 该部位已经评选为优秀或者满意案例了
                    }else{
                        if($v1['selection_results']=='2' || $v1['selection_results']=='3'){
                            if($v1['graduation_type']=='1'){
                                // 优秀或者满意 且已经毕业
                                array_push($position_list,$v1['position_id']);
                            }else{
                                if($v1['tg_excellent_time']>$max_time){
                                    $max_time = $v1['tg_excellent_time'];
                                    $graduation_type = $v1['graduation_type'];
                                    $selection_results = $v1['selection_results'];
                                }
                            }
                        }else{
                            if($v1['tg_excellent_time']>$max_time){
                                $max_time = $v1['tg_excellent_time'];
                                $graduation_type = $v1['graduation_type'];
                                $selection_results = $v1['selection_results'];
                            }
                        }
                    }
                }

                if($max_time>$time1){
                    array_push($client_list,$v);
                }

            }


        }

        // echo "<pre>";
        // print_r($client_list);
        // exit;

        $cj_where = [];
        $cj_where1 = [];
        $cj_where2 = [];

        array_push($cj_where,['id','not in',$client_list]);
        array_push($cj_where,['cj_time','>',$qichu_time]);
        array_push($cj_where,['final_consumption','<',$time1]);
        array_push($cj_where,['final_service','<',$time1]);

        // array_push($cj_where,['c.final_consumption|c.final_service','<',$time1]);
        array_push($cj_where1,['final_consumption','>','0']);
        array_push($cj_where1,['final_consumption','<',$time1]);

        array_push($cj_where2,['final_service','>','0']);
        array_push($cj_where2,['final_service','<',$time1]);

        $field          = ['id,customer_name,sex,telephone,create_time,shop_id,source_id,final_consumption,final_service,cj_time'];


        $cj_custmoer_list = Db::name('customer')->where($cj_where)
                                                ->where(function ($query) use ($time1) {
                                                    $cj_where1 = [];
                                                    $cj_where2 = [];

                                                    array_push($cj_where1,['final_consumption','>','0']);
                                                    array_push($cj_where1,['final_consumption','<',$time1]);

                                                    array_push($cj_where2,['final_consumption','>','0']);
                                                    array_push($cj_where2,['final_consumption','<',$time1]);

                                                    $query->where(function ($query) use ($cj_where1){
                                                        $query->where($cj_where1);
                                                    });
                                                    $query->whereOr(function ($query) use ($cj_where2){
                                                        $query->where($cj_where2);
                                                    });

                                                })
                                                ->field($field)
                                                ->select()->toArray();
                                                // ->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,final_consumption,final_service,cj_time');

        echo "<pre>";
        print_r($cj_custmoer_list);
        exit;

    }

    /**
     * 进店导出
     */
    public function daochu_jindian()
    {

        $start_time = mktime(0,0,0,6,1,2022);
        $end_time = mktime(23,59,59,6,12,2022);

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id','id');

        // 市场
        $market_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('market_name','shop_id');


        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');


        //网络进店
        $internet=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['first_time|second_time', 'between', "$start_time,$end_time"], ['source_id', 'not in', [46,47,48,49]]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $internet=array_column($internet,null,'shop_id');


        //转介绍
        $transfer_introduction=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['first_time|second_time', 'between', "$start_time,$end_time"], ['source_id' ,'=', '49']])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $transfer_introduction=array_column($transfer_introduction,null,'shop_id');


        //外联
        $outreach=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['first_time|second_time', 'between', "$start_time,$end_time"], ['source_id', 'in', [46,47,48]]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $outreach=array_column($outreach,null,'shop_id');



        $list = [];


        foreach ($shop_list as $k => $v) {

            $one = [];

            $one['shop_market'] = $market_names[$v] ?? '';

            $one['shop_name'] = $shop_names[$v] ?? '';

            $one['internet'] = $internet[$v]['sum'] ?? '';

            $one['transfer_introduction'] = $transfer_introduction[$v]['sum'] ?? '';

            $one['outreach'] = $outreach[$v]['sum'] ?? '';

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";

        // exit;

        $callName = [

            'shop_market'   =>'市场',

            'shop_name' =>'门店',

            'internet' =>'网络进店',

            'transfer_introduction' =>'转介绍',

            'outreach' =>'门头外联',

        ];

        $downUrl = exportExcelData('进店数导出',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    /**
     * 录入导出—新增顾客
     */
    public function daochu_xinzeng()
    {
        echo "导出新增";

        // echo "<pre>";
        // print_r($request);
        // echo "</pre>";
        // exit;

        $start_time = strtotime('2024-05-01 00:00:00');
        $end_time = strtotime('2024-05-31 23:59:59');
        $biaoqianCode = '658';

        $where = [];

        array_push($where,['type','<>','2']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);
        array_push($where,['market_id','=','6']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id','id');

        $where = [];

        // if(!empty($request)){
        //     array_push($where,['id','in',$request]);
        // }

        // $xianshang = [43,44,118,50,51,52,54,55,56,53,584,609];
        // $xianshang = [54,51];
        $xianshang = [52];
        // $kefu_list = [518,517,516,527,521,524,523,525,519,520];
        $kefu_list = Db::name('admin_user')->where('structure_id','8')->column('id','realname');

        // $shop_list = [76520948050011,73782774834649,73782774813542,73782774859952,73782774880422,73782774896742,73782774813357,73782774822370,73782774881835];
        // $shop_list = [78144422942919,73782776866322,78153925370743]; // 潍坊，李沧，西海岸
        // $shop_list = [73782775102252];// 平阳街
        // $shop_list = [73782774707242,73782774791259,73782774742277];// 太原市场

        // array_push($where,['member_label','like','%'.$biaoqianCode.'%']);
        // array_push($where,['create_time','>=',$start_time]);
        // array_push($where,['create_time','<=',$end_time]);
        // array_push($where,['operator','in',$kefu_list]);
        // array_push($where,['shop_id','in',$shop_list]);
        array_push($where,['source_id','in',$xianshang]);
        // array_push($where,['introducer','>',0]);
        // array_push($where,['effective_entry','=',2]);
        // array_push($where,['membership_leve','<',1]);
        // array_push($where,['be_overdue_time','>',time()]);
        // array_push($where,['first_time','>=',$start_time]);
        // array_push($where,['first_time','<=',$end_time]);
        // array_push($where,['cj_order_id','>','0']);
        // array_push($where,['repair_progress','in',[63,64]]); // 治疗中断-63，治疗完毕-64


        $customer_list = Db::name('customer')->where($where)
        // ->count();
        // ->limit(5000,5000)
        ->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,creator_id,operator,nurse_id,remarks,first_time,second_time,effective_entry,cj_time,consumption,member_label,cj_money,membership_leve,be_overdue_time,introducer,birthday,repair_progress');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $introducer = array_values(array_unique(array_column($customer_list,'introducer')));


        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        $where = [];

        array_push($where,['id','in',$introducer]);

        $introducerList = Db::name('shop_person')->where($where)->column('name','id');

        // echo "<pre>";
        // print_r($introducerList);
        // echo "</pre>";
        // exit;

        // 疤痕类型数组
        $where = [];

        array_push($where,['category_id','=',1]);

        $scarType = Db::name('setting')->where($where)->column('name','id');

        // 疤痕部位数组
        $where = [];

        array_push($where,['category_id','=',2]);

        $positionArr = Db::name('setting')->where($where)->column('name','id');

        // 来源数组
        $where = [];

        array_push($where,['category_id','=',4]);

        $laiyuanList = Db::name('setting')->where($where)->column('name','id');

        // 性别数组
        $sexList = [0=>'未知',1=>'男',2=>'女'];

        // 会员数组
        $vipList = [1=>'白玉兰',2=>'紫玉兰',3=>'金玉兰'];

        // 客服数组
        $kefuList = Db::name('admin_user')->column('realname','id');

        // 治疗进展状态
        $where = [];

        array_push($where,['category_id','=',5]);

        $repair_progress = Db::name('setting')->where($where)->column('name','id');


        $list = [];

        foreach($customer_list as $key => $val){

            $scarInfo = Db::name('customer_scar')->where('customer_id',$val['id'])->select()->toArray();

            // $customer_setting_id_type = $scarInfo[0]['customer_setting_id_type'] ?? "";

            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['sex'] = $sexList[$val['sex']];

            $one['telephone'] = $val['telephone'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

            $one['operator'] = $kefuList[$val['operator']] ?? '';;

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $one['remarks'] = $val['remarks'];

            $one['effective_entry'] = $val['effective_entry'] == 1 ? '是' : '否';

            $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';

            $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';

            $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d H:i:s',$val['cj_time']) : '';

            $one['cj_money'] = $val['cj_money'];

            $one['consumption'] = $val['consumption'];

            $one['member_label'] = $val['member_label'];

            $one['membership_leve'] = $vipList[$val['membership_leve']] ?? '';

            $one['be_overdue_time'] = $val['be_overdue_time'] > 0 ? date('Y-m-d H:i:s',$val['be_overdue_time']) : '';

            $one['introducer'] = $introducerList[$val['introducer']] ?? '';

            $one['birthday'] = $val['birthday'] > 0 ? date('Y-m-d',$val['birthday']) : '';

            $scarStr = $scarInfo[0]['customer_setting_id_type'] ?? "";

            $one['repair_progress'] = $repair_progress[$val['repair_progress']] ?? '';

            $positionStr = $scarInfo[0]['customer_setting_id_position'] ?? "";

            if($scarStr){

                $one['customer_scar'] = $scarType[$scarStr] ?? '';

            }else{

                $one['customer_scar'] = '';

            }


            if($positionStr){

                $one['customer_position'] = $positionArr[$positionStr] ?? '';

            }else{

                $one['customer_position'] = '';

            }

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        // exit;

        $callName = [

            'customer_name'   =>'姓名',

            'shop_name' =>'门店',

            'operator' =>'客服',

            'nurse_name' =>'护理师',

            'sex' =>'性别',

            'telephone'     =>'手机号',

            'birthday'    =>'生日',

            'source_name'   =>'来源',

            'create_time'    =>'录入时间',

            'remarks'    =>'备注',

            'effective_entry'    =>'是否有效进店',

            'first_time'    =>'首次进店',

            'second_time'    =>'二次进店',

            'cj_time'    =>'成交时间',

            'cj_money'    =>'成交金额',

            'consumption'    =>'累计消费金额',

            'member_label'    =>'会员标签',

            'membership_leve'    =>'会员等级',

            'be_overdue_time'    =>'会员到期时间',

            'introducer'    =>'转介绍员工',

            'customer_scar' => '疤痕类型',

            'customer_position' => '疤痕部位',

            'repair_progress' => '治疗进度',

        ];

        $timeStr = date('Y-m-d',time());

        $downUrl = exportExcelData('小红书',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }


    /**
     * 实收款
     */
    public function daochu_shishou()
    {
        echo "导出实收";

        $start_time = mktime(0,0,0,6,1,2022);
        $end_time = mktime(23,59,59,6,12,2022);

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id','id');

        // 市场
        $market_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('market_name','shop_id');


        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');

        // echo "<pre>";
        // print_r($shop_list);
        // echo "</pre>";

        // zhengchangshishou


        // echo "<pre>";
        // print_r($sumShopPayMoney);
        // echo "</pre>";
        // exit;

        $list = [];


        foreach ($shop_list as $k => $v) {

            $one = [];

            $one['shop_market'] = $market_names[$v] ?? '';

            $one['shop_name'] = $shop_names[$v] ?? '';

            $pay_where = [];

            array_push($pay_where,['status','=',2]);

            array_push($pay_where,['refund_type','=','101.02']);

            array_push($pay_where,['deal_time','between',"$start_time,$end_time"]);

            array_push($pay_where,['shop_id','=',"$v"]);

            $sumShopPayMoney = Db::name('shop_refund_order')->fieldRaw('sum(pay_amount * coefficient) as pay_amount')->where('type','=',3)->where($pay_where)->find();

            $sumShopRefundMoney = Db::name('shop_refund_order')->fieldRaw('sum(pay_amount * coefficient) as pay_amount')->where('type','=',1)->where($pay_where)->find();

            // echo "<pre>";
            // print_r($sumShopPayMoney);
            // echo "</pre>";
            // exit;

            $one['shishou'] = round($sumShopPayMoney['pay_amount']-$sumShopRefundMoney['pay_amount'],2);

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";

        $callName = [

            'shop_market'   => '市场',

            'shop_name' => '门店',

            'shishou' => '实收',

        ];

        $downUrl = exportExcelData('实收金额',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    /**
     * 护理量统计
     */
    public function huliliang_tongji()
    {
        echo "huliliangtongji";

        $start_time = mktime(0,0,0,3,1,2024);
        $end_time = mktime(23,59,59,3,31,2024);

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id','id');

        $staff_where = [];
        array_push($staff_where,['status','=','1']);
        array_push($staff_where,['shop_id','in',$shop_list]);

        $staff_list = DB::name('shop_person')->where($staff_where)->column('name','id');



        $service_where = [];
        array_push($service_where,['create_time','between',"$start_time,$end_time"]);
        array_push($service_where,['nurse_id_one|nurse_id_two','in',$staff_list]);
		
		array_push($service_where,['station_name','<>','']);

        $service_list = DB::name('service_log')->where($service_where)->column('id,service_id,nurse_id_one,nurse_id_two,shop_id','nurse_id_one,create_time,customer_id,station_name');

		//echo count($service_list);exit;
        // echo "<pre>";
        // print_r($service_list);
        // echo "</pre>";

        $shop_ids = array_values(array_unique(array_column($service_list,'shop_id')));

		$nurse_id_one = array_values(array_unique(array_column($service_list,'nurse_id_one')));

        $nurse_id_two = array_values(array_unique(array_column($service_list,'nurse_id_two')));

        $customer_ids = array_values(array_unique(array_column($service_list,'customer_id')));



        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');

        $where = [];

        array_push($where,['id','in',$nurse_id_one]);

        $nurseList_one = Db::name('shop_person')->where($where)->column('name','id');

        $where = [];

        array_push($where,['id','in',$nurse_id_two]);

        $nurseList_two = Db::name('shop_person')->where($where)->column('name','id');

        $where = [];

        array_push($where,['id','in',$customer_ids]);

        $customerList = Db::name('customer')->where($where)->column('customer_name','id');

// exit;
        $list = [];

        foreach($service_list as $key => $val){
            $one = [];

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['customer_name'] = $customerList[$val['customer_id']] ?? '' ;

            $one['nurse_name1'] = $nurseList_one[$val['nurse_id_one']] ?? '';

            $one['nurse_name2'] = $nurseList_two[$val['nurse_id_two']] ?? '';
			
			$one['station_name'] = $val['station_name'] ?? '';



            array_push($list,$one);
        }

        // echo "<pre>";
        // print_r($list);
        // exit;

        $callName = [

            'create_time'   =>'核销时间',

            'shop_name' =>'核销门店',

            'customer_name' =>'顾客',

            'nurse_name1' =>'护理师1',

            'nurse_name2' =>'护理师2',
			
			'station_name' =>'店长核销',

        ];

        $downUrl = exportExcelData('核销明细表',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    /**
     * 新增顾客卡项
     */
    public function tiaozheng_fuwukaxiang()
    {
        echo '新增顾客卡项';

        // exit;
        if (empty($_FILES['file'])) {

            echo '请选择上传文件<br><br><a href="http://burn.178590.com/return/gengxin_servicecard.html">返回【会员基本信息导入】页面</a>';exit;

        }

        $tmp_name = $_FILES['file']['tmp_name'];

        $importData = readExcelData($tmp_name);

        echo "<pre>";
        print_r($importData);
        echo "</pre>";

        $shopID = Db::name('shop')->where('shop_name',$importData[0]['11'])->value('shop_id');

        // 顾客id
        $tel_ids = array_values(array_unique(array_column($importData,'1')));

        $where = [];

        array_push($where,['telephone','in',$tel_ids]);

        array_push($where,['shop_id','=',$shopID]);

        $id_list = Db::name('customer')->where($where)->column('id','telephone');

        // 卡项名称翻译
        $service_name_list = array_values(array_unique(array_column($importData,'5')));

        $where = [];

        array_push($where,['name','in',$service_name_list]);

        $goods_list = Db::name('shop_goods')->where($where)->column('id','name');

        $where = [];

        array_push($where,['name','in',$service_name_list]);

        $category_list = Db::name('shop_goods')->where($where)->column('category_id','name');

        // 开卡员工id
        $nurse_names = array_values(array_unique(array_column($importData,'10')));

        $where = [];

        array_push($where,['name','in',$nurse_names]);

        array_push($where,['shop_id','=',$shopID]);

        $nurse_list = Db::name('shop_person')->where($where)->column('id','name');

        // echo "<pre>";
        // print_r($goods_list);
        // echo "</pre>";
        // exit;

        $addData = [];

        $msg = 'success';

        $error_msg = [];

        foreach ($importData as $key => $value) {
            $key_num = $key+1;
            $msg = 'success';
            $id = $id_list[$value['1']] ?? $msg='error！ 第'.$key_num.'行————手机号:'.$value['1'].'————找不到顾客ID！！！';

            if($msg != 'success'){
                array_push($error_msg,$msg);
                $msg = 'success';
            }

            $good_id = $goods_list[$value['5']] ?? $msg='error！ 第'.$key_num.'行————商品名称:'.$value['5'].'————错误！！！';

            if($msg != 'success'){
                array_push($error_msg,$msg);
                $msg = 'success';
            }

            $nurse_id = $nurse_list[$value['10']] ?? $msg=$msg='error！ 第'.$key_num.'行————员工姓名:'.$value['10'].'————查询不到ID！！！';

            if($msg != 'success'){
                array_push($error_msg,$msg);
                $msg = 'success';
            }

            $data = [
                'name'         => $value['5'],
                'shop_id'      => $shopID,
                'customer_id'  => $id,
                'service_type' => $value['7'] == '按次' ? 2 : 1,
                'num'          => $value['8'],
                'start_time'   => $value['7'] == '按次' ? '' : intval(strtotime($value[0].' 00:00:00')),
                'end_time'     => $value['7'] == '按次' ? '' : intval(strtotime($value[9].' 00:00:00')),
                'create_time'  => $value['0'] > 0 ? intval(strtotime($value[0].' 00:00:00')) : $msg='error',
                'status'       => '1',
                'experience'   => '0',
                'kk_user_id'   => $nurse_id,
                'kk_money'     => '0',
                'category_id'  => $category_list[$value['5']] ?? '',
                'goods_id'     => $good_id
            ];

            array_push($addData,$data);

            // echo $value['4']."的id是：".$id;
            // echo "<br/>";

            // echo $value['5']."的id是：".$good_id;
            // echo "<br/>";

            // echo $value['10']."的id是：".$nurse_id;
            // echo "<br/>";

        }

        // echo "<pre>";
        // print_r($addData);
        // echo "</pre>";

        if(empty($error_msg)){
            echo "可以新增";
            $res = Db::name("service")->insertAll($addData);
            echo '导入成功数据:'.$res;
        }else{
            echo "<pre>";
            print_r($error_msg);
        }

    }



    public function daochu_yubeihuiyuan()
    {
        echo "导出预备会员";

        $where = [];
        array_push($where,['shop_id','=','73782775246614']);
        array_push($where,['member_classification','in',[71,72,73]]);
        array_push($where,['membership_leve','=',0]);

        $customer_list = Db::name('customer')->where($where)->column('id,customer_name,telephone,cj_time,shop_id,consumption,nurse_id');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $marketList = Db::name('shop')->where($where)->column('market_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');
        // exit;

        $list = [];
        foreach($customer_list as $key => $val){
            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['telephone'] = $val['telephone'];

            $one['market_name'] = $marketList[$val['shop_id']] ?? '';

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d H:i:s',$val['cj_time']) : '';

            $one['consumption'] = $val['consumption'];

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);

        $callName = [

            'customer_name'   =>'姓名',

            'telephone' =>'手机',

            'market_name' =>'市场',

            'shop_name' =>'门店',

            'nurse_name' =>'护理师',

            'cj_time'     =>'成交时间',

            'consumption'   =>'累计消费',

        ];

        $downUrl = exportExcelData('预备会员导出',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }


    /**
     * 录入导出—未成交顾客
     */
    public function daochu_weichengjiao()
    {
        echo "导出新增";

        // $start_time = mktime(0,0,0,1,1,2019);
        // $end_time = mktime(23,59,59,12,31,2019);

        $start_time = strtotime('2023-01-01 00:00:00');
        $end_time = strtotime('2024-12-31 23:59:59');

        $where = [];

        array_push($where,['type','in',[1,3]]);
        array_push($where,['shop_id','<>','74076159037951']);
        array_push($where,['status','=','1']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id');

        $where = [];

        $scar = Db::name('setting')->where('category_id','=','1')->column('id');

        // echo "<pre>";
        // print_r($scar);
        // echo "</pre>";
        // exit;
        // $xianshang = [43,44,118,50,51,52,54,55,56,53,584];

        $shop_list = [73782775012531];
        // $shop_list = [78144422942919,73782776866322,78153925370743]; // 潍坊，李沧，西海岸

        // array_push($where,['create_time','>',$start_time]);
        // array_push($where,['create_time','<',$end_time]);
        array_push($where,['shop_id','in',$shop_list]);
        array_push($where,['first_time|second_time','between',"$start_time,$end_time"]);
        // array_push($where,['first_time','=','0']);
        // array_push($where,['first_time','>','0']);
        // array_push($where,['cj_time','<','500']);
        // array_push($where,['consumption','=','0.00']);
        array_push($where,['consumption','<','500.00']);
        // array_push($where,['age','between',"1,20"]);
        // array_push($where,['source_id','<>','597']);

        // array_push($where,['source_id','in',$xianshang]);

// ,3,4,5,6,7,8,9,10,655,656
        $customer_list = Db::name('customer')
                        ->where('id', 'in',function ($query) {
                            $query->table('xy_customer_scar')->where('customer_setting_id_type','in',[1,2])->field('customer_id');
                        })
                        ->where($where)
                        // ->limit(10000,5000)
                        ->column('id,customer_name,sex,telephone,first_time,age,shop_id,source_id,creator_id,operator,nurse_id,second_time,consumption,create_time');
                        // ->count();

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        // 疤痕类型数组
        $where = [];

        array_push($where,['category_id','=',1]);

        $scarType = Db::name('setting')->where($where)->column('name','id');

        // 来源数组
        $where = [];

        array_push($where,['category_id','=',4]);

        $laiyuanList = Db::name('setting')->where($where)->column('name','id');

        // 性别数组
        $sexList = [0=>'未知',1=>'男',2=>'女'];

        // 客服数组
        $kefuList = Db::name('admin_user')->column('realname','id');


        $list = [];

        foreach($customer_list as $key => $val){

            $scarInfo = Db::name('customer_scar')->where('customer_id',$val['id'])->select()->toArray();

            $one = [];

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $one['customer_name'] = $val['customer_name'];

            $one['sex'] = $sexList[$val['sex']];

            $one['telephone'] = $val['telephone'];

            $one['age'] = $val['age'];

            $one['consumption'] = $val['consumption'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

            $one['operator'] = $kefuList[$val['operator']] ?? '';;

            $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';

            $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';

            $scarStr = $scarInfo[0]['customer_setting_id_type'] ?? "";

            if($scarStr){

                $one['customer_scar'] = $scarType[$scarStr] ?? '';

            }else{

                $one['customer_scar'] = '';

            }


            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        // exit;

        $callName = [

            'create_time'   =>'新增时间',

            'customer_name'   =>'姓名',

            'shop_name' =>'门店',

            'operator' =>'客服',

            'nurse_name' =>'护理师',

            'sex' =>'性别',

            'age' =>'年龄',

            'telephone'     =>'手机号',

            'source_name'   =>'来源',

            'customer_scar' =>'疤痕类型',

            'first_time'    =>'首次进店时间',

            'second_time'    =>'二次进店时间',

            'consumption'    =>'累计消费金额',

        ];

        $downUrl = exportExcelData('进店未成交顾客明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";


    }

    /**
     * 录入导出—未进店顾客
     */
    public function daochu_weijindian()
    {
        echo "导出未进店";

        $start_time = strtotime('2023-01-01 00:00:00');
        $end_time = strtotime('2024-04-30 23:59:59');

        $where = [];

        array_push($where,['type','in',[1,3]]);
        array_push($where,['shop_id','<>','74076159037951']);
        array_push($where,['status','=','1']);

        // echo "<pre>";
        // print_r($where);
        // echo "</pre>";

        $shop_list = DB::name('shop')->where($where)->column('shop_id');

        // echo "<pre>";
        // print_r($shop_list);
        // echo "</pre>";
        // exit;

        $where = [];

        // $xianshang = [43,44,118,50,51,52,54,55,56,53,584];

        // $xianshang = [54];


        $shop_list = [73782775133028,73782775155221,73782775102252,73782775013838,73782775012531,73782775096910];

        array_push($where,['shop_id','in',$shop_list]);
        // array_push($where,['create_time','>=',$start_time]);
        array_push($where,['create_time','<=',$end_time]);
        array_push($where,['first_time','=','0']);
        array_push($where,['second_time','=','0']);
        array_push($where,['consumption','=','0.00']);

        // array_push($where,['age','between',"1,20"]);
        // array_push($where,['source_id','<>','597']);


        // array_push($where,['source_id','in',$xianshang]);


        $customer_list = Db::name('customer')->where($where)
                            // ->count();
                            ->limit(5500,5500)
                            ->column('id,customer_name,sex,age,telephone,first_time,shop_id,source_id,creator_id,operator,nurse_id,second_time,consumption,create_time');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        // 疤痕类型数组
        $where = [];

        array_push($where,['category_id','=',1]);

        $scarType = Db::name('setting')->where($where)->column('name','id');

        // 来源数组
        $where = [];

        array_push($where,['category_id','=',4]);

        $laiyuanList = Db::name('setting')->where($where)->column('name','id');

        // 性别数组
        $sexList = [0=>'未知',1=>'男',2=>'女'];

        // 客服数组
        $kefuList = Db::name('admin_user')->column('realname','id');


        $list = [];

        foreach($customer_list as $key => $val){

            $scarInfo = Db::name('customer_scar')->where('customer_id',$val['id'])->select()->toArray();

            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['sex'] = $sexList[$val['sex']];

            $one['telephone'] = $val['telephone'];

            $one['age'] = $val['age'];

            $one['consumption'] = $val['consumption'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

            $one['operator'] = $kefuList[$val['operator']] ?? '';;

            $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';

            $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $scarStr = $scarInfo[0]['customer_setting_id_type'] ?? "";

            if($scarStr){

                $one['customer_scar'] = $scarType[$scarStr] ?? '';

            }else{

                $one['customer_scar'] = '';

            }


            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        // exit;

        $callName = [

            'customer_name'   =>'姓名',

            'shop_name' =>'门店',

            'operator' =>'客服',

            'nurse_name' =>'护理师',

            'sex' =>'性别',

            'age' =>'年龄',

            'telephone'     =>'手机号',

            'source_name'   =>'来源',

            'customer_scar' =>'疤痕类型',

            'first_time'    =>'首次进店时间',

            'second_time'    =>'二次进店时间',

            'consumption'    =>'累计消费金额',

            'create_time'    =>'新增时间',

        ];

        $downUrl = exportExcelData('未进店顾客明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";


    }


    /**
     * 入会顾客信息统计
     */
    public function jion_vip_customer()
    {
        echo "导出入会顾客信息";

        $start_time = mktime(0,0,0,4,1,2022);
        $end_time = mktime(23,59,59,4,30,2022);

        $where = [];

        // $xianshang = [43,44,118,50,51,52,54,55,56,53,584];

        // $shop_list = [73782775155221];
        // array_push($where,['create_time','>',$start_time]);

        // array_push($where,['shop_id','in',$shop_list]);
        // array_push($where,['first_time|second_time','>','0']);
        // join_time
        array_push($where,['join_time','between',"$start_time,$end_time"]);
        // array_push($where,['source_id','in',$xianshang]);


        $customer_list = Db::name('customer')->where($where)->column('id,customer_name,telephone,join_time,shop_id');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_list = array_values(array_unique(array_column($customer_list,'shop_id')));

        // echo "<pre>";
        // print_r($shop_list);
        // echo "</pre>";
        // exit;

        // 市场
        $market_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('market_name','shop_id');


        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');


        $list = [];

        foreach($customer_list as $key => $val){
            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['telephone'] = $val['telephone'];

            $one['market_name'] = $market_names[$val['shop_id']];

            $one['shop_name'] = $shop_names[$val['shop_id']];

            $one['join_time'] = $val['join_time'] > 0 ? date('Y-m-d',$val['join_time']) : '';

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        // exit;

        $callName = [

            'customer_name'   =>'姓名',

            'telephone'     =>'手机号',

            'market_name' =>'市场',

            'shop_name' =>'门店',

            'join_time'    =>'入会时间',

        ];

        $downUrl = exportExcelData('4月入会顾客信息',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";


    }

    public function zidingyi2()
    {
        $shopId = '73782774707242';
        $startTime = strtotime('2022-06-01 00:00:00');

        $startTime1 = strtotime('2022-04-01 00:00:00');
        $endTime = strtotime('2022-06-30 23:59:59');

        $good_where = [];

        array_push($good_where,['create_time','between',"$startTime1,$endTime"]);
        array_push($good_where,['refund_mode','<>','06']);

        // $goods = Db::name('shop_refund_order_goods')->where($good_where)->column('order_id');

        // echo "<pre>";
        // print_r($goods);
        // exit;

        $receive = Db::name('shop_refund_order')->alias('r')

            ->join('shop_refund_order_goods g', 'g.order_id = r.id', 'LEFT')



            // ->field('r.pay_amount,r.coefficient')

            ->where('r.shop_id','=',$shopId)

            ->when($startTime ?? 0, function ($query) use ($startTime) {

                return $query->whereTime('r.deal_time', '>=', $startTime);

            })

            ->when($endTime ?? 0, function ($query) use ($endTime) {

                return $query->whereTime('r.deal_time', '<=', $endTime);

            },function ($query) use ($endTime) {

                return $query->whereMonth('r.deal_time');

            })

            // ->where($is_shop_where)

            // ->where($where)

            ->where('g.refund_mode','<>','06')

            // ->where('r.id','in',$goods)

            ->where('r.type','=',3)

            ->where('r.status','=',2)

            ->where('r.refund_type','=','101.02')

            ->column('r.pay_amount * r.coefficient as pay_amount','r.id');

            // ->field('r.pay_amount * r.coefficient as pay_amount,r.id')

            // ->find();

        echo "<pre>";
        print_r($receive);
        echo "</pre>";

        echo $all = array_sum($receive);

        exit;
        // $where = [];

        // array_push($where,['type','=','1']);
        // array_push($where,['shop_name','not in','营销中心']);
        // array_push($where,['status','=','1']);
        // array_push($where,['shop_type','=','2']);

        // $shop_list = DB::name('shop')->where($where)->column('shop_id','id');

        // $shop_list = [73782774982253];

        // $sTime = strtotime('2022-01-01');
        // $start_time = strtotime('2022-05-01');
        // $end_time = strtotime('2022-05-31');

        // // 新增会员顾客数
        // $xz_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"]])->field('id,join_time,customer_name,telephone')->select()->toArray();

        // echo "<pre>";
        // print_r($xz_huiyuan_list);


        ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        // echo "预约未进店";
        // $start_time_int = strtotime('2021-05-01');
        // $end_time_int = strtotime('2022-04-30');

        // $where = [];
        // array_push($where,['create_time','>=',$start_time_int]);
        // array_push($where,['create_time','<=',$end_time_int]);
        // array_push($where,['effective_entry','=','2']);

        // $count = Db::name('customer')->where($where)->count();
        // echo "21年5月1日-22年4月30日预约未进店顾客数：".$count;

        // echo "<br/>";

        // $where = [];
        // array_push($where,['first_time|second_time','between',"$start_time_int,$end_time_int"]);
        // array_push($where,['cj_time','=','0']);

        // $count = Db::name('customer')->where($where)
        // ->column('id,customer_name,telephone');
        // // ->count();
        // // echo "21年5月1日-22年4月30日进店未成交顾客数：".$count;

        // echo "<pre>";
        // print_r($count);

        // $year = date('Y');

        // $moneth = date('m');

        // $changdu    = date('t', strtotime($year."-".$moneth."-1"));

        // $start_time_int = strtotime($year."-".$moneth."-1");

        // $end_time_int = strtotime($year."-".$moneth."-".$changdu);

        // $where = [];

        // // 文化路 73782774813357
        // $shop_list = [73782774822370];

        // array_push($where,['shop_id','in',$shop_list]);
        // array_push($where,['member_classification','in',[71,72,73]]);
        // array_push($where,['membership_leve','=',0]);

        // array_push($where,['final_consumption|final_service','between',"$start_time_int,$end_time_int"]);

        // $field          = ['id', 'customer_name', 'telephone','member_classification','membership_leve'];
        // // 达到条件顾客数
        // // [['shop_id', 'in', $shop_list], ['member_classification', 'between', [71,72,73]],['membership_leve','=',0]]
        // $cj_list=Db::name('customer')->where($where)
        // // ->field('count(id) as sum,shop_id')->group('shop_id')
        // ->field($field)
        // ->select()->toArray();

        // echo "<pre>";
        // print_r($cj_list);

        // // $cj_num=array_column($cj_list,null,'shop_id');




        ///////////////////////-----------------------------------------------------------------------------------------------------//////////////////////
        echo "服务卡项导出";

        $field = ['id','name','shop_id','customer_id','service_type','num','create_time','start_time','end_time'];

        // 按次
        $where = [];
        array_push($where,['shop_id','=','73782775249465']);
        // array_push($where,['service_type','=','2']);
        array_push($where,['status','=','1']);

        $service_list = Db::name('service')->field($field)->where($where)
        // ->limit(20000, 10000)
        ->select()->toArray();

        // echo "<pre>";
        // print_r($service_list);
        // exit;

        $card_ids = array_values(array_unique(array_column($service_list,'id')));

        $shop_ids = array_values(array_unique(array_column($service_list,'shop_id')));

        $customer_ids = array_values(array_unique(array_column($service_list,'customer_id')));


        // 卡项
        $where = [];

        array_push($where,['service_id','in',$card_ids]);

        $service_log_list = Db::name('service_log')->where($where)->group('service_id')->column('count(id)','service_id');

        // echo "<pre>";
        // print_r($service_log_list);
        // exit;

        // 门店
        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name,market_name','shop_id');

        // 顾客
        $where = [];

        array_push($where,['id','in',$customer_ids]);

        $customerList = Db::name('customer')->where($where)->column('customer_name,telephone,king_no,nurse_id','id');

        $nurse_ids = array_values(array_unique(array_column($customerList,'nurse_id')));

        // 员工
        $where = [];

        array_push($where,['id','in',$nurse_ids]);

        $nurseList = Db::name('shop_person')->where($where)->column('id,name');

        // echo "<pre>";
        // print_r($nurseList);

        // $callName = [

        //     'id' => 'ID',

        //     'name'   =>'姓名',
        // ];

        // $downUrl = exportExcelData('员工',$callName,$nurseList,'xlsx',1);

        // echo "<script language='javascript'>";

        // echo " location='".$downUrl."';";

        // echo "</script>";
        // exit;

        // echo "<pre>";
        // print_r($customerList);

        // 类型数组
        $type = [1=>'按期',2=>'按次'];

        $list = [];

        foreach($service_list as $key => $val){
            $one = [];

            $one['id'] = $val['id'];

            $one['name'] = $val['name'];

            $one['market_name'] = $shopList[$val['shop_id']]['market_name'] ?? '';

            $one['shop_name'] = $shopList[$val['shop_id']]['shop_name'] ?? '';

            $one['customer_name'] = $customerList[$val['customer_id']]['customer_name'] ?? '';

            $one['telephone'] = $customerList[$val['customer_id']]['telephone'] ?? '';

            $one['king_no'] = $customerList[$val['customer_id']]['king_no'] ?? '';

            // $nurseID = $customerList[$val['customer_id']]['nurse_id'] ?? '';

            // if($nurseID>0){
            //     $one['nurse_name'] = $nurseList[$nurseID];
            // }else{
            //     $one['nurse_name'] = '';
            // }

            $one['start_time'] = $val['start_time'] > 0 ? date('Y-m-d',$val['start_time']) : '';

            $one['end_time'] = $val['end_time'] > 0 ? date('Y-m-d',$val['end_time']) : '';

            $one['nurse_name'] = $customerList[$val['customer_id']]['nurse_id'] ?? '';
            // $one['nurse_name'] = $nurseList[$customerList[$val['customer_id']]['nurse_id']] ?? '';

            $one['type'] = $type[$val['service_type']];

            $one['num'] = $val['num'];

            $one['use_num'] = $service_log_list[$val['id']] ?? '0';

            $one['sy_num'] = $one['num'] - $one['use_num'];

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d',$val['create_time']) : '';


            array_push($list,$one);
        }

        // echo "<pre>";
        // print_r($list);
        // exit;

        $callName = [

            'id' => '卡项ID',

            'name'   =>'卡项名称',

            'market_name'   =>'市场名',

            'shop_name'   =>'门店',

            'customer_name'   =>'姓名',

            'telephone'     =>'手机号',

            'king_no' => '顾客K3',

            'nurse_name' => '护理师',

            'type' => '开卡类型',

            'num'    =>'开卡次数',

            'use_num'    =>'使用次数',

            'sy_num'    =>'剩余次数',

            'create_time'    =>'开卡时间',

            'start_time'    =>'卡项开始时间',

            'end_time'    =>'卡项结束时间',

        ];

        $downUrl = exportExcelData('解放店卡项详情',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

        // $one = '15000';
        // $two = '49999';
        // $where = [];
        // array_push($where,['consumption','between',"$one,$two"]);
        // array_push($where,['member_classification','<>','72']);

        // $list = Db::name('customer')->where($where)->column('id,customer_name,member_classification,consumption');
        // echo "<pre>";
        // print_r($list);

        // foreach ($list as $key => $value) {
        //     $updateArr['member_classification'] = '72';

        //     Db::name('customer')->where('id','=',$value['id'])->update($updateArr);
        // }


    }

    public function vip_info()
    {
        echo "vip_info";

        $where = [];

        $shop_list = [73782775131388];

        // array_push($where,['membership_leve','>','0']);
        // array_push($where,['create_time','>',$start_time]);
        // array_push($where,['create_time','<',$end_time]);
        array_push($where,['shop_id','in',$shop_list]);
        // array_push($where,['source_id','in',$xianshang]);


        $customer_list = Db::name('customer')->where($where)->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,creator_id,operator,nurse_id,remarks,first_time,second_time,effective_entry,cj_time,consumption,member_label,membership_leve,join_time,be_overdue_time,fcustId,king_no');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        // 来源数组
        $where = [];

        array_push($where,['category_id','=',4]);

        $laiyuanList = Db::name('setting')->where($where)->column('name','id');

        // 性别数组
        $sexList = [0=>'未知',1=>'男',2=>'女'];

        // 客服数组
        $kefuList = Db::name('admin_user')->column('realname','id');


        $list = [];

        foreach($customer_list as $key => $val){
            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['sex'] = $sexList[$val['sex']];

            $one['telephone'] = $val['telephone'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

            $one['operator'] = $kefuList[$val['operator']] ?? '';;

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $one['remarks'] = $val['remarks'];

            $one['effective_entry'] = $val['effective_entry'] == 1 ? '是' : '否';

            $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';

            $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';

            $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d H:i:s',$val['cj_time']) : '';

            $one['consumption'] = $val['consumption'];

            $one['membership_leve'] = $val['membership_leve'];

            $one['join_time'] = $val['join_time'] > 0 ? date('Y-m-d',$val['join_time']) : '';

            $one['be_overdue_time'] = $val['be_overdue_time'] > 0 ? date('Y-m-d',$val['be_overdue_time']) : '';

            $one['fcustId'] = $val['fcustId'];

            $one['king_no'] = $val['king_no'];

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);

        $callName = [

            'customer_name'   =>'姓名',

            'shop_name' =>'门店',

            'operator' =>'客服',

            'nurse_name' =>'护理师',

            'sex' =>'性别',

            'telephone'     =>'手机号',

            'source_name'   =>'来源',

            'create_time'    =>'录入时间',

            'remarks'    =>'备注',

            'effective_entry'    =>'是否有效进店',

            'first_time'    =>'首次进店',

            'second_time'    =>'二次进店',

            'cj_time'    =>'成交时间',

            'consumption'    =>'累计消费金额',

            'membership_leve'    =>'会员等级',

            'join_time'    =>'入会日期',

            'be_overdue_time'    =>'会员到期',

            'fcustId'    =>'内码',

            'king_no'    =>'K3',

        ];

        $downUrl = exportExcelData('客户明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }


    public function zidingyi3()
    {
        $start_time = mktime(0,0,0,3,15,2022);
        $end_time = mktime(23,59,59,6,30,2022);

        $save=[
            'operator' => '519',
        ];

        $where = [];

        // array_push($where,['shop_id','=','73782774881835']);

        array_push($where,['operator','=','519']);

        array_push($where,['create_time','between',"$start_time,$end_time"]);

        // array_push($where,['first_time','=','0']);

        // array_push($where,['second_time','=','0']);

        // array_push($where,['source_id','in',[44,53,56]]);

        // Db::name('customer')->where($where)->update($save);

        $list = Db::name('customer')->where($where)->select()->toArray();

        echo "<pre>";
        print_r($list);
        echo "</pre>";
    //     echo "zidingyi3";

    //     $startTime = strtotime("2022-05-01");

    //     $endTime = strtotime("2022-05-31");

    //     $staff_array = [267,268,269,270,333];

    //     $where = [];

    //     array_push($where,['id','in',$staff_array]);

    //     $person_names = Db::name('shop_person')->where($where)->column('name','id');

    //     echo "<pre>";
    //     print_r($person_names);

    //     // 单独护理次数
    //     $where = [];

    //     array_push($where,['create_time','between',"$startTime,$endTime"]);

    //     array_push($where,['nurse_id_one','in',$staff_array]);

    //     array_push($where,['nurse_id_two','=',0]);

    //     $service_log_one = Db::name('service_log')->where($where)->group('nurse_id_one')->column('count(id)','nurse_id_one');

    //     echo "<pre>";
    //     print_r($service_log_one);

    //     // 合作护理次数
    //     $where = [];

    //     array_push($where,['create_time','between',"$startTime,$endTime"]);

    //     array_push($where,['nurse_id_two','in',$staff_array]);

    //     // array_push($where,['nurse_id_two','=',0]);

    //     $service_log_two = Db::name('service_log')->where($where)->group('nurse_id_two')->column('count(id)','nurse_id_two');

    //     echo "<pre>";
    //     print_r($service_log_two);

    //     $where = [];

    //     array_push($where,['create_time','between',"$startTime,$endTime"]);

    //     array_push($where,['nurse_id_one','in',$staff_array]);

    //     array_push($where,['nurse_id_two','<>',0]);

    //     $service_log_three = Db::name('service_log')->where($where)->group('nurse_id_one')->column('count(id)','nurse_id_one');

    //     echo "<pre>";
    //     print_r($service_log_three);

    //     $list = [];
    //     foreach ($service_log_one as $k => $v) {
    //         $one = [];

    //         $one['name'] = $person_names[$k];

    //         $one['num'] = $v;

    //         $num1 = $service_log_two[$k] ?? '';

    //         $num2 = $service_log_three[$k] ?? '';

    //         if($num1){
    //             $one['num'] = bcadd($one['num'],$num1*0.5);
    //         }

    //         if($num2){
    //             $one['num'] = bcadd($one['num'],$num2*0.5);
    //         }

    //         array_push($list,$one);
    //     }

    //     echo "<pre>";
    //     print_r($list);

    }

    public function update_shop_id()
    {
        echo "修改余额表门店归属";echo "<br/>";

        $shop_id = Db::name('shop')->where('shop_name','=','环球店')->value('shop_id');

        echo "门店id".$shop_id;echo "<br/>";

        // 余额表顾客的门店
        $list = Db::name('customer_balance')
                ->where('shop_id','=',$shop_id)
                ->column('shop_id','customer_id');

        // echo "<pre>";
        // print_r($list);
        // exit;
        echo "总共顾客数：".count($list);echo "<br/>";

        $update_ids = [];
        $updateNum = 0;
        $num = 0;
        foreach ($list as $key => $value) {
            $customer_shop_id = Db::name('customer')->where('id','=',$key)->column('id,shop_id');

            if(empty($customer_shop_id)){
                $delete_where = [];

                array_push($delete_where,['customer_id','=',$key]);

                Db::name('customer_balance')->where($delete_where)->delete();

                array_push($delete_where,['time','in',[20220529,20220530,20220531,20220601,20220602]]);

                Db::name('customer_balance_tj')->where($delete_where)->delete();

            }else{
                if($customer_shop_id[0]['shop_id'] == $value){
                    $num++;
                }else{
                    $updateNum++;
                    array_push($update_ids,$key);
                    $save=[
                        'shop_id'=>$customer_shop_id[0]['shop_id'],
                    ];

                    $bl_where = [];
                    array_push($bl_where,['customer_id','=',$key]);

                    Db::name('customer_balance')->where($bl_where)->update($save);

                    $tj_where = [];

                    array_push($tj_where,['customer_id','=',$key]);
                    array_push($tj_where,['time','in',[20220531,20220601,20220602]]);

                    Db::name('customer_balance_tj')->where($tj_where)->update($save);
                }
            }


        }

        echo "<pre>";
        print_r($update_ids);

        echo "不用修改".$num;echo "<br/>";
        echo "修改".$updateNum;echo "<br/>";

    }

    public function daochu_refund_order()
    {
        echo "oooooooo";
        echo "<br/>";
        $start_time = strtotime('2022-07-01 00:00:00');
        echo "<br/>";
        $end_time = strtotime('2022-07-31 23:59:59');
        // echo "<br/>";exit;

        $market_names = ['成都市场','重庆市场','昆明市场','南宁市场','昆明门诊市场'];
        // $market_names = ['南宁市场','昆明门诊市场'];

        $shop_where = [];

        array_push($shop_where,['market_name','in',$market_names]);
        array_push($shop_where,['type','=','1']);

        $shop_list = Db::name('shop')->where($shop_where)->column('shop_id');

        // echo "<pre>";
        // print_r($shop_list);
        // echo "</pre>";

        $where = [];

        array_push($where,['r.deal_time','between',"$start_time,$end_time"]);
        array_push($where,['r.shop_id','in',$shop_list]);


        $order = Db::name('shop_refund_order')->alias('r')
                    ->join('shop_refund_order_goods g', 'r.id=g.order_id', 'LEFT')
                    ->where($where)
                    ->column('g.pay_price,g.refund_mode_name,r.refund_type,r.coefficient,r.id,r.shop_name,r.uid,r.consignee,r.business_type,r.order_type,r.deal_time,r.create_time,r.type,r.operator_name,r.refund_order_no');

        // echo "<pre>";
        // print_r($order);
        // echo "</pre>";

        $customer_ids = array_values(array_unique(array_column($order,'uid')));

        $customer_info = Db::name('customer')->where('id','in',$customer_ids)->column('customer_name,telephone','id');

        $business_type = ['1' => '疤痕','2' => '项目'];

        $order_type = ['1' => '新单','2' => '补单'];

        $list = [];
        foreach ($order as $key => $value) {

            $refund_type = $value['type'] == '1' ? ['101.01'=>' 店面转诊','101.02'=>' 退款','101.03'=>' 退款调整'] : ['101.01'=>' 店面转诊','101.02'=>' 收款','101.03'=>' 收款调整'];

            $one = [];

            $one['customerName'] = $customer_info[$value['uid']]['customer_name'] ?? '';
            $one['telephone'] = $customer_info[$value['uid']]['telephone'] ?? '';
            $one['refund_order_no'] = $value['refund_order_no'] ?? '';
            $one['refund_mode_name'] = $value['refund_mode_name'] ?? '';
            $one['pay_price'] = $value['pay_price'] ?? '';
            $one['refund_type'] = $refund_type[$value['refund_type']] ?? '';
            $one['business_type'] = $business_type[$value['business_type']] ?? '';
            $one['order_type'] = $order_type[$value['order_type']] ?? '';
            $one['deal_time'] = date('Y-m-d',$value['deal_time']) ?? '';
            $one['create_time'] = date('Y-m-d',$value['create_time']) ?? '';
            $one['operator_name'] = $value['operator_name'] ?? '';
            $one['shop_name'] = $value['shop_name'] ?? '';


            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";

        $callName = [
            'refund_order_no' =>'订单编号',

            'customerName'   =>'姓名',

            'telephone' =>'手机号',

            'pay_price' =>'金额',

            'refund_mode_name' =>'收款方式',

            'refund_type'     =>'订单类型',

            'business_type'   =>'疤痕/项目',

            'order_type'    =>'新单/补单',

            'deal_time'    =>'收款时间',

            'create_time'    =>'录单时间',

            'operator_name'    =>'录单人',

            'shop_name'    =>'录单门店',

        ];

        $downUrl = exportExcelData('收款明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    public function zidingyi4()
    {
        $cjTime = strtotime('2022-01-01 00:00:00');
        $byTime = strtotime('-4 month');

        $shop_where = [];

        array_push($shop_where,['type','=','1']);
        array_push($shop_where,['shop_type','=','2']);
        array_push($shop_where,['shop_id','<>','74076159037951']);

        $shop_list = Db::name('shop')->where($shop_where)->order('market_name asc')->column('shop_id');

        $shop_name = Db::name('shop')->where($shop_where)->column('shop_name','shop_id');

        // echo "<pre>";
        // print_r($shop_name);
        // echo "</pre>";

        $bharray = ['1','2','3','4','5','6','7','8','10'];
        $xmarray = ['9','11','12'];

        $customerField = 'c.count(id)';

        $list = [];
        foreach ($shop_list as $key => $value) {
            $one = [];
            // 疤痕保有顾客
            $resultBH = Db::name('customer')->alias('c')
                    ->join('xy_customer_scar cs', 'cs.customer_id=c.id', 'LEFT')
                    ->where('cs.customer_setting_id_type','in',$bharray)
                    ->where('c.cj_time','>',$cjTime)
                    ->where('c.shop_id','=',$value)
                    ->where('c.final_consumption|c.final_service','>',$byTime)
                    ->column('distinct c.id');

            // echo "<pre>";
            // print_r($resultBH);
            // echo "</pre>";

            $bhbycustomer = implode(',',$resultBH);
            // echo $bhbycustomer;

            // 项目保有顾客
            $resultXM = Db::name('customer')->alias('c')
                    ->join('xy_customer_scar cs', 'cs.customer_id=c.id', 'LEFT')
                    ->where('cs.customer_setting_id_type','in',$xmarray)
                    ->where('c.cj_time','>',$cjTime)
                    ->where('c.shop_id','=',$value)
                    ->where('c.final_consumption|c.final_service','>',$byTime)
                    ->column('distinct c.id');

            // echo "<pre>";
            // print_r($resultXM);
            // echo "</pre>";

            $xmbycustomer = implode(',',$resultXM);
            // echo $xmbycustomer;
            // echo "<br/>";

            $refund_where = [];

            // 疤痕消费
            $refundBH1 = Db::name('shop_refund_order')
                    ->where('business_type','=','1')
                    ->where('deal_time','>=',$cjTime)
                    ->where('uid','in',$bhbycustomer)
                    ->where('status','=','2')
                    ->where('type','=','3')
                    ->where('refund_type','=','101.02')
                    ->column('sum(pay_amount)');

            $refundBH2 = Db::name('shop_refund_order')
                    ->where('business_type','=','1')
                    ->where('deal_time','>=',$cjTime)
                    ->where('uid','in',$bhbycustomer)
                    ->where('status','=','2')
                    ->where('type','=','1')
                    ->where('refund_type','=','101.02')
                    ->column('sum(pay_amount)');

            // exit;
            // echo "<pre>";
            // print_r($refundBH1[0]);
            // echo "</pre>";
            $refundBH = bcsub($refundBH1[0],$refundBH2[0],2);
            // echo "<br/>";
            // echo $refundBH;

            // 项目消费
            $refundXM1 = Db::name('shop_refund_order')
                    ->where('business_type','=','2')
                    ->where('deal_time','>=',$cjTime)
                    ->where('uid','in',$bhbycustomer)
                    ->where('status','=','2')
                    ->where('type','=','3')
                    ->where('refund_type','=','101.02')
                    ->column('sum(pay_amount)');

            $refundXM2 = Db::name('shop_refund_order')
                    ->where('business_type','=','2')
                    ->where('deal_time','>=',$cjTime)
                    ->where('uid','in',$bhbycustomer)
                    ->where('status','=','2')
                    ->where('type','=','1')
                    ->where('refund_type','=','101.02')
                    ->column('sum(pay_amount)');

            // exit;
            // echo "<pre>";
            // print_r($refundBH1[0]);
            // echo "</pre>";
            $refundXM = bcsub($refundXM1[0],$refundXM2[0],2);
            // echo "<br/>";
            // echo $refundXM;
            $one['shopName'] = $shop_name[$value]; // 门店名称
            $one['resultBH'] = count($resultBH); // 疤痕保有人数
            $one['resultXM'] = count($resultXM); // 项目保有人数
            $one['refundBH'] = $refundBH; // 疤痕保有顾客消费
            $one['refundXM'] = $refundXM; // 项目保有顾客消费
            // exit;
            array_push($list,$one);
        }



        echo "<pre>";
        print_r($list);
        echo "</pre>";

        $callName = [
            'shopName' =>'门店名称',

            'resultBH'   =>'疤痕保有人数',

            'refundBH' =>'疤痕保有顾客消费',

            'resultXM' =>'项目保有人数',

            'refundXM' =>'项目保有顾客消费',

        ];

        $downUrl = exportExcelData('2022上半年保有报表',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    public function get_access_token(){

        echo "获取access_token";

        $url = "https://api.weixin.qq.com/cgi-bin/token";

        // $url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=wxfee2f64c9d301f2f&secret=26385eaeace3711d002ba80ec7bd3a0e";

        $jsonData = [

            'grant_type' => 'client_credential',

            'appid'      => 'wxfee2f64c9d301f2f',

            'secret'     => '26385eaeace3711d002ba80ec7bd3a0e',

        ];

        $res = $this->zidingyi5($url,$jsonData);

        var_dump($res);

    }

    public function zidingyi5($url,$jsonData)
    {
        // 请求函数 参数($url,$jsonData)
        $ch = curl_init();
	    curl_setopt($ch, CURLOPT_POST, 1);
	    curl_setopt($ch, CURLOPT_URL, $url);
	    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	    // https 支持 - 对认证证书来源的检查
	    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	    // 请求数据放这里
	    curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonData);
	    // head
	    // curl_setopt($ch, CURLOPT_HTTPHEADER, $head);

        $response = curl_exec($ch);

        return $response;

        // ---------------------------------------分割线----------------------------------
        // return $cishu+1;
    }

    public function zidingyi6()
    {
        
        $where = [];

        array_push($where,['type','in',[1,3]]);
        array_push($where,['shop_id','<>','74076159037951']);
        array_push($where,['status','=','1']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id');

        $where = [];

        array_push($where,['status','=',3]);
        array_push($where,['leave_at','>',"2024-05-01"]);
        array_push($where,['shop_id','in',$shop_list]);

        $leave_person = Db::name('shop_person')->where($where)->column('id');

        $customer = Db::name('customer')->where('nurse_id','in',$leave_person)->column('shop_id');

        $shop_ids = array_unique($customer);

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);
        array_push($where,['status','=',1]);
        array_push($where,['station_id','=',13]);

        $zl = Db::name('shop_person')->where($where)->column('id');

        foreach ($zl as $key => $value) {
            
            Db::name('shop_person')->where('id','=',$value)->update(['$status' => 2]);

        }

        


    }

    public function get_paykey()
    {
        $request = request()->param();

        $order_id = $request['refund_order_no'] ?? 0;

        $where = [];

        array_push($where,['refund_order_no','=',$order_id]);

        $oredr_info = Db::name('shop_refund_order')->where($where)->find();

        $shop_where = [];

        array_push($shop_where,['shop_id','=',$oredr_info['shop_id']]);

        $shop_info = Db::name('shop')->where($shop_where)->find();

        if($oredr_info['shanghu_type'] == '1'){
            // 公户

            $cusid = $shop_info['shop_gonghu_cusid'];

            $appid = $shop_info['shop_gonghu_appid'];

            $private_key = $shop_info['shop_gonghu_privateKey'];

        }else{
            // 私户

            $cusid = $shop_info['shop_sihu_cusid'];

            $appid = $shop_info['shop_sihu_appid'];

            $private_key = $shop_info['shop_sihu_privateKey'];

        }

        $payRequest = [

            'pay_amount' =>$oredr_info['pay_amount'],

            'refund_order_no' => $oredr_info['refund_order_no'],

            'description' => $oredr_info['description'],

            'cusid' => $cusid,

            'appid' => $appid,

            'private_key' => $private_key,

            'shop_name' => '欣奕除疤'.$shop_info['city_name'].$shop_info['shop_name'],

            'shop_id' => $oredr_info['shop_id'],

            'shanghu_type' => $oredr_info['shanghu_type'],

        ];

        // echo "<pre>";
        // print_r($payRequest);
        // echo "</pre>";
        // exit;
        return resultArray(['data' => $payRequest]);


    }

    public function daochu_2022_09()
    {
        echo "20220930";

        $is_export = 1;

        $qichu_time = strtotime('2021-11-01 00:00:00');

        $time1 = strtotime('-4 month');

        // 优案且已经毕业的顾客
        $yx_where = [];
        // $my_where = [];

        array_push($yx_where,['graduation_type','=','1']);
        array_push($yx_where,['selection_results','in',[2,3]]);

        $yx_customer_list = Db::name('case')->where($yx_where)->column('distinct customer_id');



        $client_list = [];

        foreach ($yx_customer_list as $k => $v) {
            $where = [];

            array_push($where,['customer_id','=',$v]);

            $caseList = Db::name('case')->where($where)->column('id,customer_id,selection_results,tg_excellent_time,graduation_type,position_id,position_name');

            $anli_num = count($caseList);

            // echo "<pre>";
            // print_r($caseList);

            if($anli_num<2){
                array_push($client_list,$v);
            }else{
                $position_list=[];// 部位
                $max_time = 0;// 最新评选时间
                $graduation_type = 0;// 毕业状态
                $selection_results = 0;// 评选结果
                foreach ($caseList as $k1 => $v1) {
                    if(in_array($v1['position_id'],$position_list)){
                        // 该部位已经评选为优秀或者满意案例了
                    }else{
                        if($v1['selection_results']=='2' || $v1['selection_results']=='3'){
                            if($v1['graduation_type']=='1'){
                                // 优秀或者满意 且已经毕业
                                array_push($position_list,$v1['position_id']);
                            }else{
                                if($v1['tg_excellent_time']>$max_time){
                                    $max_time = $v1['tg_excellent_time'];
                                    $graduation_type = $v1['graduation_type'];
                                    $selection_results = $v1['selection_results'];
                                }
                            }
                        }else{
                            if($v1['tg_excellent_time']>$max_time){
                                $max_time = $v1['tg_excellent_time'];
                                $graduation_type = $v1['graduation_type'];
                                $selection_results = $v1['selection_results'];
                            }
                        }
                    }
                }

                if($max_time>$time1){
                    array_push($client_list,$v);
                }

            }

        }

        // echo "<pre>";
        // print_r($client_list);
        // exit;

        $this->daochu_xinzeng($client_list);

    }

    public function daochu_case()
    {
        // 十月申请且通过优秀或者满意案例
        $start_time = strtotime('2022-10-01 00:00:00');
        $end_time = strtotime('2022-10-31 23:59:59');

        $where = [];

        array_push($where,['sp_excellent_time','>=',$start_time]);
        array_push($where,['sp_excellent_time','<=',$end_time]);

        array_push($where,['sq_type','in',[1,2]]);
        array_push($where,['selection_results','in',[2,3]]);

        $case_list = Db::name('case')->where($where)->select()->toArray();

        $customer_ids = array_values(array_unique(array_column($case_list,'customer_id')));

        $shop_ids = array_values(array_unique(array_column($case_list,'shop_id')));

        $nurse_ids = array_values(array_unique(array_column($case_list,'nurse_id')));

        $reviewer_ids = array_values(array_unique(array_column($case_list,'reviewer_id'))); // 审核人

        $where = [];
        array_push($where,['id','in',$customer_ids]);
        $customerList = Db::name('customer')->where($where)->column('customer_name,sex','id');

        // echo "<pre>";
        // print_r($shop_ids);
        // echo "</pre>";
        // exit;

        $shop_where = [];
        array_push($shop_where,['shop_id','in',$shop_ids]);
        $shopList = Db::name('shop')->where($shop_where)->column('shop_name,market_name','shop_id');

        // echo "<pre>";
        // print_r($shopList);
        // echo "</pre>";
        // exit;

        $where = [];
        array_push($where,['id','in',$nurse_ids]);
        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        $where = [];
        array_push($where,['id','in',$reviewer_ids]);
        $adminList = Db::name('admin_user')->where($where)->column('realname','id');

        $list = [];
        foreach ($case_list as $key => $value) {
            $one = [];

            $one['sp_excellent_time'] = date('Y-m-d',$value['sp_excellent_time']);
            $one['customer_name'] = $customerList[$value['customer_id']]['customer_name'];
            $one['sex'] = $customerList[$value['customer_id']]['sex'];
            $one['position_name'] = $value['position_name'];
            $one['reason_name'] = $value['reason_name'];
            $one['type_name'] = $value['type_name'];
            $one['nurse_name'] = $nurseList[$value['nurse_id']];
            $one['shop_name'] = $shopList[$value['shop_id']]['shop_name'];
            $one['market_name'] = $shopList[$value['shop_id']]['market_name'];
            $one['selection_results'] = $value['selection_results'];
            $one['reviewer_name'] = $adminList[$value['reviewer_id']];
            $one['tg_excellent_time'] = date('Y-m-d',$value['tg_excellent_time']);

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;

        $callName = [

            'sp_excellent_time'   =>'申请时间',

            'customer_name' =>'客户姓名',

            'sex' =>'性别',

            'position_name' =>'申请部位',

            'reason_name' =>'疤痕成因',

            'type_name'     =>'疤痕类型',

            'nurse_name'   =>'护理师',

            'shop_name'    =>'归属门店',

            'market_name'    =>'归属市场',

            'selection_results'    =>'评选结果',

            'reviewer_name'    =>'审核人',

            'tg_excellent_time'    =>'通过时间',

        ];

        // echo "<pre>";
        // print_r($callName);
        // echo "</pre>";
        // exit;

        $timeStr = date('Y-m-d',time());

        $downUrl = exportExcelData('2022-10优秀满意案例通过明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    public function daochu_tag(){
        echo "导出标签";

        // $biaoqianCode = '614';

        $biaoqianCode = $_POST['laiyuan'];

        $tagName = Db::name('setting')->where('id',$biaoqianCode)->value('name');

        $where = [];



        array_push($where,['member_label','like','%'.$biaoqianCode.'%']);



        $customer_list = Db::name('customer')->where($where)
        ->column('id,customer_name,sex,telephone,create_time,shop_id,source_id,creator_id,operator,nurse_id,remarks,first_time,second_time,effective_entry,cj_time,consumption,member_label,cj_money,membership_leve,be_overdue_time,introducer');

        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));

        $introducer = array_values(array_unique(array_column($customer_list,'introducer')));


        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        $where = [];

        array_push($where,['id','in',$introducer]);

        $introducerList = Db::name('shop_person')->where($where)->column('name','id');

        // echo "<pre>";
        // print_r($introducerList);
        // echo "</pre>";
        // exit;

        // 疤痕类型数组
        $where = [];

        array_push($where,['category_id','=',1]);

        $scarType = Db::name('setting')->where($where)->column('name','id');

        // 疤痕部位数组
        $where = [];

        array_push($where,['category_id','=',2]);

        $positionArr = Db::name('setting')->where($where)->column('name','id');

        // 来源数组
        $where = [];

        array_push($where,['category_id','=',4]);

        $laiyuanList = Db::name('setting')->where($where)->column('name','id');

        // 性别数组
        $sexList = [0=>'未知',1=>'男',2=>'女'];

        // 会员数组
        $vipList = [1=>'白玉兰',2=>'紫玉兰',3=>'金玉兰'];

        // 客服数组
        $kefuList = Db::name('admin_user')->column('realname','id');


        $list = [];

        foreach($customer_list as $key => $val){

            $scarInfo = Db::name('customer_scar')->where('customer_id',$val['id'])->select()->toArray();

            // $customer_setting_id_type = $scarInfo[0]['customer_setting_id_type'] ?? "";

            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['sex'] = $sexList[$val['sex']];

            $one['telephone'] = $val['telephone'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['source_name'] = $laiyuanList[$val['source_id']] ?? '未知';

            $one['operator'] = $kefuList[$val['operator']] ?? '';;

            $one['create_time'] = $val['create_time'] > 0 ? date('Y-m-d H:i:s',$val['create_time']) : '';

            $one['remarks'] = $val['remarks'];

            $one['effective_entry'] = $val['effective_entry'] == 1 ? '是' : '否';

            $one['first_time'] = $val['first_time'] > 0 ? date('Y-m-d H:i:s',$val['first_time']) : '';

            $one['second_time'] = $val['second_time'] > 0 ? date('Y-m-d H:i:s',$val['second_time']) : '';

            $one['cj_time'] = $val['cj_time'] > 0 ? date('Y-m-d H:i:s',$val['cj_time']) : '';

            $one['cj_money'] = $val['cj_money'];

            $one['consumption'] = $val['consumption'];

            $one['member_label'] = $val['member_label'];

            $one['membership_leve'] = $vipList[$val['membership_leve']] ?? '';

            $one['be_overdue_time'] = $val['be_overdue_time'] > 0 ? date('Y-m-d H:i:s',$val['be_overdue_time']) : '';

            $one['introducer'] = $introducerList[$val['introducer']] ?? '';

            $scarStr = $scarInfo[0]['customer_setting_id_type'] ?? "";

            $positionStr = $scarInfo[0]['customer_setting_id_position'] ?? "";

            if($scarStr){

                $one['customer_scar'] = $scarType[$scarStr] ?? '';

            }else{

                $one['customer_scar'] = '';

            }


            if($positionStr){

                $one['customer_position'] = $positionArr[$positionStr] ?? '';

            }else{

                $one['customer_position'] = '';

            }

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        // exit;

        $callName = [

            'customer_name'   =>'姓名',

            'shop_name' =>'门店',

            'operator' =>'客服',

            'nurse_name' =>'护理师',

            'sex' =>'性别',

            'telephone'     =>'手机号',

            'source_name'   =>'来源',

            'create_time'    =>'录入时间',

            'remarks'    =>'备注',

            'effective_entry'    =>'是否有效进店',

            'first_time'    =>'首次进店',

            'second_time'    =>'二次进店',

            'cj_time'    =>'成交时间',

            'cj_money'    =>'成交金额',

            'consumption'    =>'累计消费金额',

            'member_label'    =>'会员标签',

            'membership_leve'    =>'会员等级',

            'be_overdue_time'    =>'会员到期时间',

            'introducer'    =>'转介绍员工',

            'customer_scar' => '疤痕类型',

            'customer_position' => '疤痕部位',


        ];

        $timeStr = date('Y-m-d',time());

        $downUrl = exportExcelData($tagName.'访客户明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }


    // 流失顾客 ps:3个月内未有消费记录或是服务记录，且未打相应标签的顾客数量
    public function loss_customers($ids,$date)
    {

        // $shop_id = '73782774791259';

        $tagTime = strtotime($date.' -3 month');

        $startTime = '0';

        // echo "起始时间：".$tagTime;

        $where = [];

        // array_push($where,['cj_time','>','0']);
        // array_push($where,['cj_money','>=','500.00']);

        // array_push($where,['shop_id','=',$shop_id]);

        array_push($where,['id','in',$ids]);

        array_push($where,['final_consumption','>=',$startTime]);
        array_push($where,['final_consumption','<',$tagTime]);

        array_push($where,['final_service','>=',$startTime]);
        array_push($where,['final_service','<',$tagTime]);

        array_push($where,['final_consumption|final_service','>',$startTime]);

        // array_push($where,['member_label','not like','%588%']);

        // array_push($where,['member_label','not like','%589%']);

        $customer_list = Db::name('customer')->where($where)->column('id');

        // echo count($customer_list);
        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";
        // exit;

        // return count($customer_list);
        return $customer_list;

    }

    // 成交顾客数
    public function cj_customer($shop_id)
    {

        $where = [];

        // $cj_start = strtotime('2023-01-01');
        $cj_start = '0';

        array_push($where,['cj_time','>',$cj_start]);
        array_push($where,['cj_money','>=','500.00']);
        // array_push($where,['cj_money','<=','1000.00']);

        array_push($where,['final_consumption|final_service','>','0']);

        array_push($where,['shop_id','=',$shop_id]);

        $cjCustomerIds = Db::name('customer')->where($where)->column('id');

        return $cjCustomerIds;

    }

    // 流失顾客 ps:3个月内有消费记录或是服务记录;
    public function baoyou_customer($ids,$date)
    {

        $tagTime = strtotime($date.' -3 month');

        $where = [];

        // array_push($where,['cj_time','>','0']);
        // array_push($where,['cj_money','>=','500.00']);

        // array_push($where,['shop_id','=',$shop_id]);

        array_push($where,['id','in',$ids]);
        array_push($where,['final_consumption|final_service','>',$tagTime]);

        $customer_list = Db::name('customer')->where($where)->column('id');

        // return count($customer_list);
        return $customer_list;

    }

    // 门店保有流失顾客数量导出
    public function daochu_liushibaoyou(){

        $date = '2023-03-16';

        $where = [];

        array_push($where,['type','=',1]);

        // array_push($where,['shop_id','=','73782774813542']);

        array_push($where,['status','=',1]);

        array_push($where,['shop_type','=',2]);

        array_push($where,['shop_id','<>','74076159037951']);

        // array_push($where,['shop_id','=','73782774813357']);

        $shopIds = Db::name('shop')->where($where)->column('shop_id,shop_name');

        // $shopIds = [73782774813357];

        // echo "<pre>";
        // print_r($shopIds);
        // echo "</pre>";
        // exit;

        $list = [];

        $BY_cids = [];

        foreach ($shopIds as $key => $value) {

            $one = [];

            $cj_customer_ids = $this->cj_customer($value['shop_id']);

            $lossNum = $this->loss_customers($cj_customer_ids,$date);

            $baoyouNum = $this->baoyou_customer($cj_customer_ids,$date);

            // echo "<pre>";
            // print_r($cj_customer_ids);
            // echo "</pre>";

            // echo "<pre>";
            // print_r($lossNum);
            // echo "</pre>";

            // echo "<pre>";
            // print_r($baoyouNum);
            // echo "</pre>";

            // exit;


            // 统计
            $one['shop_name'] = $value['shop_name'];

            $one['cj_num'] = count($cj_customer_ids);

            $one['lossNum'] = count($lossNum);

            $one['baoyouNum'] = count($baoyouNum);

            array_push($list,$one);

            // // 明细
            // foreach ($baoyouNum as $k => $v) {

            //     array_push($BY_cids,$v);

            // }



        }

        // $shop['73782774896742'] = '天明路';
        // $shop['73782774813357'] = '文化路';

        // $customerInfos = Db::name('customer')->where('id','in',$BY_cids)->column('customer_name,telephone,shop_id,final_consumption,final_service');



        // foreach ($customerInfos as $key => $value) {
        //     $customerInfos[$key]['shop_name'] = $shop[$value['shop_id']];
        //     $customerInfos[$key]['final_consumption'] = $value['final_consumption']>0 ? date('Y-m-d H:i:s',$value['final_consumption']) : "";
        //     $customerInfos[$key]['final_service'] = $value['final_service']>0 ? date('Y-m-d H:i:s',$value['final_service']) : "";
        // }

        // echo "<pre>";
        // print_r($list);
        // echo "</pre>";
        // exit;

        // $callName = [

        //     'customer_name'   =>'客户姓名',

        //     'telephone' => '手机号',

        //     'shop_name' =>'门店',

        //     'final_consumption' =>'最新消费时间',

        //     'final_service' =>'最新服务时间',

        // ];

        $callName = [

            'shop_name'   =>'门店',

            'cj_num' => '成交顾客数',

            'lossNum' =>'流失顾客数',

            'baoyouNum' =>'保有顾客数',

        ];

        $downUrl = exportExcelData('成交保有流失报表',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    // 成交对比顾客
    public function zidingyi7()
    {
        echo "zidingyi7";

        $request = request()->param();

        // echo "<pre>";
        // print_r($request);
        // echo "</pre>";
        // exit;
        

        $model = new OrderStatistics();

        $data = $model->getOrderList2($request);

        echo "<pre>";
        print_r($data);
        echo "</pre>";
        exit;

        $callName = [
            'name'   =>'市场名称',
            'shop_name' =>'门店名称',
            'experience_deal' =>'体验成交数',
            'experience_rate' =>'体验成交率',
            'receive_deal' =>'成交个数',
            'turnover_rate' =>'总成交率',
            'internet' =>'进店数量-网络',
            'transfer_introduction' =>'进店数量-转介绍裂变',
            'outreach'   =>'进店数量-门头外联',
            'enter_total_num' =>'进店数量-合计',
            'experience_total' =>'体验个数',
            'sevice' =>'护理量',
            'bh_new_total' =>'疤痕新单',
            'bh_repair_total' =>'疤痕补单',
            'xm_new_total' =>'项目新单',
            'xm_repair_total' =>'项目补单',
            'hfp_new_total' =>'护肤品新单',
            'hfp_repair_total' =>'护肤品补单',
            'job_total' =>'业绩-合计',
        ];

    }

    // 流失预警顾客
    public function warning_customer($ids,$date)
    {

        $tagTime = strtotime($date.' -1 month');

        $startTime = strtotime($date.' -3 month');

        $where = [];

        array_push($where,['id','in',$ids]);

        // array_push($where,['final_consumption','>=',$startTime]);
        array_push($where,['final_consumption','<',$tagTime]);

        // array_push($where,['final_service','>=',$startTime]);
        array_push($where,['final_service','<',$tagTime]);

        array_push($where,['final_consumption|final_service','between',"$startTime,$tagTime"]);

        array_push($where,['final_consumption|final_service','>',$startTime]);

        // array_push($where,['member_label','not like','%588%']);

        // array_push($where,['member_label','not like','%589%']);

        $customer_list = Db::name('customer')->where($where)->column('id');

        // return count($customer_list);
        return $customer_list;

    }

    // 保有顾客
    public function baoyou_customer1($ids,$date)
    {

        $tagTime = strtotime($date.' -90 day');

        $where = [];

        array_push($where,['id','in',$ids]);
        array_push($where,['final_consumption|final_service','>',$tagTime]);

        $customer_list = Db::name('customer')->where($where)->column('id');

        // return count($customer_list);
        return $customer_list;

    }

    // 流失顾客
    public function lost_customer($ids,$date)
    {

        $tagTime = strtotime($date.' -90 day');

        $startTime = '0';

        $where = [];

        array_push($where,['id','in',$ids]);

        array_push($where,['final_consumption','>=',$startTime]);
        array_push($where,['final_consumption','<',$tagTime]);

        array_push($where,['final_service','>=',$startTime]);
        array_push($where,['final_service','<',$tagTime]);

        array_push($where,['final_consumption|final_service','>',$startTime]);

        // array_push($where,['member_label','not like','%588%']);

        // array_push($where,['member_label','not like','%589%']);

        $customer_list = Db::name('customer')->where($where)->column('id');

        // return count($customer_list);
        return $customer_list;

    }

    public function hecha_hexiao(){

        $date = date('Ymd');

        $shop_ids = [73782775247649,73782775295382,73782774834649,73782774813542,73782774813357];

        $where = [];

        array_push($where,['date','=',$date]);

        array_push($where,['shop_id','in',$shop_ids]);

        $baoyouCustomerIds = Db::name('customer_tj')->where($where)->column('baoyou_customer_ids');

        $customer_ids = [];

        foreach ($baoyouCustomerIds as $key => $value) {

            $ids = explode(',',$value);

            foreach ($ids as $k => $v) {

                array_push($customer_ids,$v);

            }

        }

        $customer_info = Db::name('customer')->where('id','in',$customer_ids)->column('customer_name,telephone,shop_id,sex,age,cj_time,final_consumption,final_service','id');

        $shop = Db::name('shop')->where('shop_id','in',$shop_ids)->column('shop_name','shop_id');

        $beforeOneMonth = strtotime('- 30 day');

        $where = [];

        array_push($where,['customer_id','in',$customer_ids]);

        $service_all = Db::name('service_log')->where($where)->group('customer_id')->column('count(id)','customer_id');

        array_push($where,['create_time','>',$beforeOneMonth]);

        $service_month = Db::name('service_log')->where($where)->group('customer_id')->column('count(id)','customer_id');

        $list = [];

        foreach ($customer_info as $key => $value) {

            $one = [];

            $one['customer_name'] = $customer_info[$value['id']]['customer_name'] ?? '';

            $one['telephone'] = $customer_info[$value['id']]['telephone'] ?? '';

            $one['shop_name'] = $shop[$value['shop_id']] ?? '';

            $one['sex'] = $customer_info[$value['id']]['sex'] ?? '';

            $one['age'] = $customer_info[$value['id']]['age'] ?? '';

            $one['cj_time'] = $customer_info[$value['id']]['cj_time'] ? date("Y-m-d H:i:s",$customer_info[$value['id']]['cj_time']) : "";

            $one['final_consumption'] = $customer_info[$value['id']]['final_consumption'] ? date("Y-m-d H:i:s",$customer_info[$value['id']]['final_consumption']) : "";

            $one['final_service'] = $customer_info[$value['id']]['final_service'] ? date("Y-m-d H:i:s",$customer_info[$value['id']]['final_service']) : "";

            $one['oneMonth'] = $service_month[$value['id']] ?? '';

            $one['all'] = $service_all[$value['id']] ?? '';

            array_push($list,$one);

        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;

        $callName = [

            'customer_name'  =>'姓名',

            'telephone'  =>'手机号',

            'shop_name'  =>'门店',

            'age'  =>'年龄',

            'sex'  =>'性别',

            'cj_time'  =>'成交时间',

            'final_consumption'  =>'最后消费时间',

            'final_service'  =>'最后服务时间',

            'oneMonth'  =>'30天内核销次数',

            'all'  =>'所有核销次数',

        ];

        $downUrl = exportExcelData('核销明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    public function daochu_service_log(){

        $start_time = strtotime('2023-06-01 00:00:00');

        $end_time = strtotime('2023-06-30 23:59:59');

        $where = [];

        array_push($where,['station_name','>',0]);

        array_push($where,['create_time','between',"$start_time,$end_time"]);

        $serviec_log = Db::name('service_log')->where($where)->group('station_name')->column('shop_id,station_name,count(id) as sum'); // shop_id,station_name,



        $shop_ids = array_values(array_unique(array_column($serviec_log,'shop_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');

        foreach ($serviec_log as $key => $value) {
            $serviec_log[$key]['shop_id'] = $shopList[$value['shop_id']] ?? '';
        }

        echo "<pre>";
        print_r($serviec_log);
        echo "</pre>";

        $callName = [

            'shop_id'   =>'门店',

            'station_name' =>'店长',

            'sum' =>'数量',

        ];

        // $callName = [

        //     'mobile_model'   =>'手机型号',

        //     'sum' =>'数量',

        // ];

        $timeStr = date('Y-m-d',time());

        $downUrl = exportExcelData('小程序店长出示核销码手机型号统计(6月)',$callName,$serviec_log,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    public function daochu_service_mingxi(){

        $start_time = strtotime('2023-05-01 00:00:00');

        $end_time = strtotime('2023-06-30 23:59:59');

        $shop_ids = [73782775247649,73782775295382,73782774834649,73782774813542,73782774813357];

        $where = [];

        array_push($where,['station_name','>',0]);

        array_push($where,['shop_id','in',$shop_ids]);

        array_push($where,['create_time','between',"$start_time,$end_time"]);

        $serviec_log = Db::name('service_log')->where($where)->column('id,shop_id,station_name,customer_id,create_time'); // shop_id,station_name,

        // echo "<pre>";
        // print_r($serviec_log);
        // echo "</pre>";
        // exit;

        $customerIds = array_values(array_unique(array_column($serviec_log,'customer_id')));

        $where = [];

        array_push($where,['id','in',$customerIds]);

        $customer = Db::name('customer')->where($where)->column('customer_name,telephone,shop_id,sex,age,final_service','id');

        $shop_ids = array_values(array_unique(array_column($serviec_log,'shop_id')));

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');

        $list = [];

        foreach ($serviec_log as $key => $value) {

            $one = [];

            $one['customer_name'] = $customer[$value['customer_id']]['customer_name'] ?? '';

            $one['telephone'] = $customer[$value['customer_id']]['telephone'] ?? '';

            $one['sex'] = $customer[$value['customer_id']]['sex'] ?? '';

            $one['age'] = $customer[$value['customer_id']]['age'] ?? '';

            $one['shop_id'] = $shopList[$value['shop_id']] ?? '';

            $one['station_name'] = $value['station_name'] ?? '';

            $one['create_time'] = $value['create_time'] ? date('Y-m-d H:i:s',$value['create_time']) : '';

            array_push($list,$one);
        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;

        $callName = [

            'customer_name'   =>'顾客姓名',

            'telephone'   =>'手机',

            'sex'   =>'性别',

            'age'   =>'年龄',

            'shop_id'   =>'门店',

            'station_name' =>'店长',

            'create_time' =>'核销时间',

        ];

        // $callName = [

        //     'mobile_model'   =>'手机型号',

        //     'sum' =>'数量',

        // ];

        $timeStr = date('Y-m-d',time());

        $downUrl = exportExcelData('小程序店长核销明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }


    public function staff_performance()// 员工业绩
    {
        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_id','<>','74076159037951']);
        array_push($where,['status','=','1']);
        array_push($where,['shop_type','=','2']);

        $shop_ids = Db::name('shop')->where($where)->column('shop_id');

        // echo "<pre>";
        // print_r($shop_ids);
        // echo "</pre>";

        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);
        array_push($where,['status','=',1]);

        $shop_persons = Db::name('shop_person')->where($where)->column('name,shop_id,station_name,shop_name,id');

  //       echo '<pre>';
		// print_r($shop_persons);exit;

        $model = new MiniStisticsModel();

        $list = [];

        foreach ($shop_persons as $key => $value) {

            $newRequest = [];

            $newRequest['startTime'] = strtotime('2024-01-01 00:00:00');

            $newRequest['endTime'] = strtotime('2024-06-05 23:59:59');

            $newShopIds = [];

            array_push($newShopIds,$value['shop_id']);

            $newRequest['shop_id'] = $newShopIds;

            $newStaffIds = [];

            array_push($newStaffIds,$value['id']);
			
            $newRequest['staffList'] = $newStaffIds;
			
            $newOrder = $model->newOrder($newRequest); // 新单

            $repairOrder = $model->repairOrder($newRequest); // 补单
			
			$staffcjList = $this->shop_person_cj($newRequest); // 成交个数
			
            echo "<pre>";
            print_r($staffcjList);
            echo "</pre>";//exit;

            // echo "<pre>";
            // print_r($repairOrder);
            // echo "</pre>";
            // exit;

            // $one = [];

            // $one['shop_name'] = $value['shop_name'];

            // $one['name'] = $value['name'];

            // $one['station_name'] = $value['station_name'];

            // $one['newOrder'] = $newOrder[0]['newOrder'];

            // $one['repairOrder'] = $repairOrder[0]['repairOrder'];

            // array_push($list,$one);

        }
		exit;
        echo "<pre>";
        print_r($list);
        echo "</pre>";exit;



        $callName = [

            'shop_name'   =>'门店',

            'name' =>'姓名',

            'station_name' =>'岗位',

            'newOrder' =>'新单',

            'repairOrder' =>'补单',

        ];


        $downUrl = exportExcelData('员工新补单列表（2024-01-01至2023-05-15）',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

        exit;

    }
	
	/* 查询护理师 成交个数 */
	
	public function shop_person_cj($request){
		
		echo '进入员工 成交个数统计';
		
		$start_time = $request['startTime'];
		
		$end_time = $request['endTime'];
		
		$shop_id = $request['shop_id'];
		
		$staff_ids = $request['staffList'];
		
		
		
		$list= Db::name('customer')->alias('c')
		
		            ->join('shop_refund_order s', 'c.cj_order_id=s.id', 'LEFT')
		
					->where('c.nurse_id','in',$staff_ids)
		
					->where('c.shop_id','=',$shop_id)
					
					->group('c.nurse_id')
		
					->column('count(c.id) as num','c.nurse_id');
		
		
		return $list;
		
		
	}
	
	
	
    public function shop_new_order()
    {
        $startTime = strtotime('2022-01-01 00:00:00');

        $endTime = strtotime('2023-06-30 23:59:59');

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_id','not in',[74076159037951,73782776347131]]);
        array_push($where,['status','=','1']);
        array_push($where,['shop_type','=','2']);

        $shop_ids = Db::name('shop')->where($where)->column('shop_id,shop_name');

        $tc_where = [];

        array_push($tc_where,['source_id','=','49']);

        array_push($tc_where,['first_time|second_time','between',"$startTime,$endTime"]);

        // 转介绍顾客数
        $tc_list  = Db::name('customer')->where($tc_where)->group('shop_id')->column('count(id)','shop_id');

        $zjs_list = Db::name('customer')->where($tc_where)->column('id');

        $cj_where = [];

        array_push($cj_where,['id','in',$zjs_list]);

        array_push($cj_where,['cj_time','between',"$startTime,$endTime"]);

        $cj_num = Db::name('customer')->where($cj_where)->group('shop_id')->column('count(id)','shop_id');

        $cj_order_ids = Db::name('customer')->where($cj_where)->column('cj_order_id');


        // bh
        $bh_where = [];

        array_push($bh_where,['c.source_id','=','49']);

        array_push($bh_where,['c.first_time|c.second_time','between',"$startTime,$endTime"]);

        array_push($bh_where,['cs.customer_setting_id_type','in',[1,2,3,4,5,6,7,8,9]]);

        $bh_list  = Db::name('customer')->alias('c')->join('customer_scar cs', 'c.id=cs.customer_id', 'LEFT')->where($bh_where)->group('c.shop_id')->field('count(c.id) as num,c.shop_id')->select()->toArray();

        $bh_list=array_column($bh_list, NULL, 'shop_id');





        // dd
        $dd_where = [];

        array_push($dd_where,['c.source_id','=','49']);

        array_push($dd_where,['c.first_time|c.second_time','between',"$startTime,$endTime"]);

        array_push($dd_where,['cs.customer_setting_id_type','in',[10]]);

        $dd_list  = Db::name('customer')->alias('c')->join('customer_scar cs', 'c.id=cs.customer_id', 'LEFT')->where($dd_where)->group('c.shop_id')->field('count(c.id) as num,c.shop_id')->select()->toArray();

        $dd_list=array_column($dd_list, NULL, 'shop_id');

        // xm
        $xm_where = [];

        array_push($xm_where,['c.source_id','=','49']);

        array_push($xm_where,['c.first_time|c.second_time','between',"$startTime,$endTime"]);

        array_push($xm_where,['cs.customer_setting_id_type','in',[11,12]]);

        $xm_list  = Db::name('customer')->alias('c')->join('customer_scar cs', 'c.id=cs.customer_id', 'LEFT')->where($xm_where)->group('c.shop_id')->field('count(c.id) as num,c.shop_id')->select()->toArray();

        $xm_list=array_column($xm_list, NULL, 'shop_id');



        // 转介绍金额
        $cj_money_where = [];

        array_push($cj_money_where,['id','in',$cj_order_ids]);

        // array_push($cj_money_where,['is_introduction','=',1]);

        $cj_money_list = Db::name('shop_refund_order')->where($cj_money_where)->group('shop_id')->column('SUM(pay_amount)','shop_id');

        // echo "<pre>";
        // print_r($shop_ids);
        // echo "</pre>";

        // echo "<pre>";
        // print_r($cj_num);
        // echo "</pre>";

        // exit;

        $list = [];

        foreach ($shop_ids as $key => $value) {

            $one = [];

            $one['shop_name'] = $value['shop_name'];

            $one['zjs_num'] = $tc_list[$value['shop_id']] ?? '';

            $one['bh_num'] = $bh_list[$value['shop_id']]['num'] ?? '';

            $one['dd_num'] = $dd_list[$value['shop_id']]['num'] ?? '';

            $one['xm_num'] = $xm_list[$value['shop_id']]['num'] ?? '';

            $one['cj_num'] = $cj_num[$value['shop_id']] ?? '';

            $one['cj_money'] = $cj_money_list[$value['shop_id']] ?? '';

            array_push($list,$one);

        }

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;

        $callName = [

            'shop_name'   =>'门店',

            'zjs_num' =>'转介绍进店',

            'bh_num' =>'疤痕',

            'dd_num' =>'痘痘',

            'xm_num' =>'项目',

            'cj_num' =>'成交个数',

            'cj_money' =>'新单金额',

        ];


        $downUrl = exportExcelData('转介绍导出2',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

        exit;

    }


    // 2023-01至2023-07成交率
    public function export_2023()
    {
        $request = request()->param();

        $year = $request['year'];

        $sourceNames = explode(',',$request['source']);

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);
        array_push($where,['shop_type','=','2']);

        $shop_list = DB::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['name','in',$sourceNames]);

        $sourceIds = Db::name('setting')->where($where)->column('id');

        $jd_where = []; // 进店
        $cj_where = []; // 成交

        // 时间
        $stime = strtotime($year.'-01-01 00:00:00');
        $etime = strtotime($year.'-07-31 23:59:59');

        array_push($jd_where,['c.first_time|c.second_time','between',"$stime,$etime"]);

        array_push($cj_where,['c.cj_time','between',"$stime,$etime"]);
        array_push($cj_where,['c.cj_order_id','>',"0"]);

        // 来源
        array_push($jd_where,['c.source_id','in',$sourceIds]);

        array_push($cj_where,['c.source_id','in',$sourceIds]);

        // echo "开始时间：".$stime;
        // echo "<br/>";
        // echo "结束时间：".$etime;
        // echo "<br/>";

        // echo "<pre>";
        // print_r($sourceIds);
        // echo "</pre>";

        if($sourceNames[0] == '抖音'){

            // $bd = [1,2,3,4,5,6,7,8,9,10];
            // $xm = [11,12];

            // // 疤痘
            // $jd_bh_where = $jd_where;

            // $cj_bh_where = $cj_where;

            // array_push($jd_bh_where,['cs.customer_setting_id_type','in',$bd]);

            // array_push($cj_bh_where,['cs.customer_setting_id_type','in',$bd]);

            // // 项目
            // $jd_xm_where = $jd_where;

            // $cj_xm_where = $cj_where;

            // array_push($jd_xm_where,['cs.customer_setting_id_type','in',$xm]);

            // array_push($cj_xm_where,['cs.customer_setting_id_type','in',$xm]);

            $list = [];

            foreach ($shop_list as $key => $value) {

                $jd_where1 = $jd_where;

                $cj_where1 = $cj_where;

                array_push($jd_where1,['c.shop_id','=',$key]);

                array_push($cj_where1,['c.shop_id','=',$key]);

                // 进店
                $jd_customerIds = Db::name('customer')->alias('c')
                ->where($jd_where1)->column('c.id');

                // 成交
                $cj_customerIds = Db::name('customer')->alias('c')
                ->where($cj_where1)->column('c.id');

                $one = [];

                $one['shop_name'] = $value;

                $one['jd'] = count($jd_customerIds);

                $one['cj'] = count($cj_customerIds);

                // echo "<pre>";
                // print_r($one);
                // echo "</pre>";
                // exit;

                array_push($list,$one);

            }

            // echo "<pre>";
            // print_r($list);
            // echo "</pre>";
            // exit;

            $callName = [

                'shop_name'   =>'门店',

                'jd' =>'进店',

                'cj' =>'成交',

            ];

            $laiyuan = implode('',$sourceNames);

            $downUrl = exportExcelData($year.'年直营门店'.$laiyuan.'进店成交详情',$callName,$list,'xlsx',1);

            echo "<script language='javascript'>";

            echo " location='".$downUrl."';";

            echo "</script>";

            exit;

        }else{

            $bd = [1,2,3,4,5,6,7,8,9,10];
            $xm = [11,12];

            // 疤痘
            $jd_bh_where = $jd_where;

            $cj_bh_where = $cj_where;

            array_push($jd_bh_where,['cs.customer_setting_id_type','in',$bd]);

            array_push($cj_bh_where,['cs.customer_setting_id_type','in',$bd]);

            // 项目
            $jd_xm_where = $jd_where;

            $cj_xm_where = $cj_where;

            array_push($jd_xm_where,['cs.customer_setting_id_type','in',$xm]);

            array_push($cj_xm_where,['cs.customer_setting_id_type','in',$xm]);

            $list = [];

            foreach ($shop_list as $key => $value) {

                $jd_bh_where1 = $jd_bh_where;

                $jd_xm_where1 = $jd_xm_where;

                array_push($jd_bh_where1,['c.shop_id','=',$key]);

                array_push($jd_xm_where1,['c.shop_id','=',$key]);

                // 疤痘进店
                $bd_customerIds = Db::name('customer')->alias('c')
                ->join('customer_scar cs', 'c.id = cs.customer_id', 'LEFT')
                ->where($jd_bh_where1)->column('c.id');

                // 项目进店
                $xm_customerIds = Db::name('customer')->alias('c')
                ->join('customer_scar cs', 'c.id = cs.customer_id', 'LEFT')
                ->where($jd_xm_where1)->column('c.id');

                // 成交
                $cj_bh_where1 = $cj_bh_where;

                $cj_xm_where1 = $cj_xm_where;

                array_push($cj_bh_where1,['c.shop_id','=',$key]);

                array_push($cj_xm_where1,['c.shop_id','=',$key]);

                // 疤痘成交
                $bd_cj_customerIds = Db::name('customer')->alias('c')
                ->join('customer_scar cs', 'c.id = cs.customer_id', 'LEFT')
                ->where($cj_bh_where1)->column('c.id');

                // 项目成交
                $xm_cj_customerIds = Db::name('customer')->alias('c')
                ->join('customer_scar cs', 'c.id = cs.customer_id', 'LEFT')
                ->where($cj_xm_where1)->column('c.id');

                $one = [];

                $one['shop_name'] = $value;

                $one['jd_bd'] = count($bd_customerIds);

                $one['jd_xm'] = count($xm_customerIds);

                $one['cj_bd'] = count($bd_cj_customerIds);

                $one['cj_xm'] = count($xm_cj_customerIds);

                // echo "<pre>";
                // print_r($one);
                // echo "</pre>";
                // exit;

                array_push($list,$one);

            }

            // echo "<pre>";
            // print_r($list);
            // echo "</pre>";
            // exit;

            $callName = [

                'shop_name'   =>'门店',

                'jd_bd' =>'疤痘进店',

                'cj_bd' =>'疤痘成交',

                'jd_xm' =>'项目进店',

                'cj_xm' =>'项目成交',

            ];

            $laiyuan = implode('',$sourceNames);

            $downUrl = exportExcelData($year.'年直营门店'.$laiyuan.'进店成交详情',$callName,$list,'xlsx',1);

            echo "<script language='javascript'>";

            echo " location='".$downUrl."';";

            echo "</script>";

            exit;


        }

    }

}
